Reply

Sakib bin Sharif Posted: 12-07-2009 4:16 AM

rated by 0 users

hi

i'm using SQL Server 2005 and get stuck in a puzzle and can't go farther.

it is like i have a stored procedure which returns 5 columns. i need 2 columns of that procedure  and have to insert into a table to continue my query...

here is the sample of the scenario:

CREATE PROCEDURE MyProc1 ( @ID VARCHAR(max) )
AS
BEGIN
      SELECT [Name],[Role],[Address] From MyTable WHERE Id=@ID
END
GO;
-- here the query goes --

CREATE TABLE #TEMP
(
      [Name] varchar(max)
      ,[Subject] varchar(max)
      ,[Class] varchar(max)
)
----- I need only the name from the stored procedure ------
INSERT INTO #TEMP([Name])
EXEC MyProc1 @ID='007'  --- HERE i want only the Name Column ---
DROP TABLE #TEMP

I can't  make a table which contains all the columns of the stored procedure because the number of columns of the stored procedure may change and need to add additional columns in the procedure..

in that case i have to add the additional columns in all the table which take records from that procedure and obviously it is not expected.

so all i must need is to take the selective columns from the stored procedure to fill the table.

but can't get ride off it. as if whenever i wan't to do that the following error occured:

Msg 213, Level 16, State 7, Procedure InventoryStatusByPortfolio_All, Line 107
Insert Error: Column name or number of supplied values does not match table definition.

Reply

Md Nazmul Ahsan replied on 12-08-2009 8:21 AM

rated by 0 users

Hi Sakib,

I have done it for sort but do not use for select columns. Hope that can be done too. Please check it out.

We need to do instead is dynamically string that contains our SQL query we want to execute. After string has been built up, we can execute the composed SQL query, from an ASP or ASP.NET Web page, we'd simply call the stored procedure setting the input parameter.

 --------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE getStudents (@SelectString varchar(150) ) AS 

-- Create a variable @SelectStatement

DECLARE @SelectStatement varchar(255)    

-- Enter the dynamic Select statement into the variable @SelectStatement 

SELECT @SelectStatement = 'SELECT '+ @SelectString + ' from student'

-- Execute the SQL statement

EXEC(@SelectStatement)
 --------------------------------------------------------------------------------------------------------------

Let me know what's happen.

Thanks

Reply

MD.MONJURUL HABIB replied on 12-10-2009 2:15 AM

rated by 0 users

CREATE PROCEDURE MyProc1 (@SelectString varchar(150) ,@ID VARCHAR(50)) AS
DECLARE @SelectStatement varchar(255)
SELECT @SelectStatement = 'SELECT '+ @SelectString + ' from MyTable Where Id=' + @ID + ''

PRINT @SelectStatement

EXEC(@SelectStatement)
==========================================================================

CREATE TABLE #TEMP
(
[Name] varchar(max)
,[Subject] varchar(max)
,[Class] varchar(max)
)

INSERT INTO #TEMP([Name])
--EXEC RETURN_VALUE @ID=1 --- HERE i want only the Name Column ---
EXEC MyProc1 'Name','2'


DROP TABLE #TEMP

Reply

Sakib bin Sharif replied on 12-23-2009 4:38 AM

rated by 0 users

thanks :) it works in that way but the problem occurs when i go for a complex selection..

i'm posting my last selection of the procedure... and i can't send the whole selection statement...

SELECT

#TRADE.PORTFOLIOID

,#TRADE.TICKER

,#TRADE.AVG_BUY_COST

,#TRADE.CP AS 'CURRENT_MARKET_PRICE'

,#TRADE.CP_DATE AS 'MARKET_PRICE_DATE'

,#TRADE.REMAIN

,#TRADE.CHNG

,#TRADE.[%CHNG]

,#TRADE.WEIGHT_N

,#TRADE.MATURED

,#TRADE.TOTAL_COST_PRICE

,#TRADE.TOTAL_MKT_PRICE

,#TRADE.TOTAL_MKT_PRICE - #TRADE.TOTAL_COST_PRICE AS 'TOTAL_UNREALIZED_GAIN'

,#TRADE.FIRST_TRADEID

,#TRADE.FIRST_TRADEDATE

,#TRADE.LAST_REMAINZEROID

,#TRADE.MAX_MKT_PRICE

,#TRADE.MIN_MKT_PRICE

,#TRADE.MAX_MKT_PRICE-#TRADE.MIN_MKT_PRICE AS 'PRICE_CHANGE_SINCE_HOLDING'

,PL.PORTFOLIONAME

,CI.SHORT_NAME AS 'COMPANY_NAME'

,'<b>'+CI.TICKER+'</b>'+'<br/>'+CI.SHORT_NAME AS 'COMPANY'

,IND.INDUSTRY_NAME AS 'SECTOR_NAME'

FROM #TRADE

LEFT JOIN COMPANYINFORMATION CI ON #TRADE.TICKER=CI.TICKER

LEFT JOIN PORTFOLIOLIST PL ON #TRADE.PORTFOLIOID=PL.PORTFOLIOID

LEFT JOIN INDUSTRYCATEGORY IND ON CI.INDUSTRY_ID=IND.INDUSTRY_CODE

ORDER BY IND.INDUSTRY_NAME,#TRADE.TICKER

So I can’t send the whole selection in the parameter. Because then I should send the following as a parameter value.

SELECT

#TRADE.PORTFOLIOID

,#TRADE.TICKER

,#TRADE.AVG_BUY_COST

,#TRADE.CP AS 'CURRENT_MARKET_PRICE'

,#TRADE.CP_DATE AS 'MARKET_PRICE_DATE'

,#TRADE.REMAIN

,#TRADE.CHNG

,#TRADE.[%CHNG]

,#TRADE.WEIGHT_N

,#TRADE.MATURED

,#TRADE.TOTAL_COST_PRICE

,#TRADE.TOTAL_MKT_PRICE

,#TRADE.TOTAL_MKT_PRICE - #TRADE.TOTAL_COST_PRICE AS 'TOTAL_UNREALIZED_GAIN'

,#TRADE.FIRST_TRADEID

,#TRADE.FIRST_TRADEDATE

,#TRADE.LAST_REMAINZEROID

,#TRADE.MAX_MKT_PRICE

,#TRADE.MIN_MKT_PRICE

,#TRADE.MAX_MKT_PRICE-#TRADE.MIN_MKT_PRICE AS 'PRICE_CHANGE_SINCE_HOLDING'

,PL.PORTFOLIONAME

,CI.SHORT_NAME AS 'COMPANY_NAME'

,'<b>'+CI.TICKER+'</b>'+'<br/>'+CI.SHORT_NAME AS 'COMPANY'

,IND.INDUSTRY_NAME AS 'SECTOR_NAME'