Passing Dynamic SQL to Stored Procedure

[Desi555]

Hi,

I am using a temp table in a stored procedure, I need to pass the column list to it

I am passing the sql statement using this parameter
@ReportSQL varchar(1000)

The problem is I can not use this parameter in the following statement

INSERT INTO #TempVouchers(serial_num, Name, Address, MID, CID)
SELECT @ReportSQL

Following is the error message I am getting:
"The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns."

How can I solve this issue?

Thanks!

 

[JeanNiBee]

Try using exec( @somevariable).

Ex:

SELECT @statement = "INSERT INTO #TempVouchers(serial_num, Name, Address, MID, CID)"
exec( @statement + @ReportSQL )


Now I'm not saying you should do it this way, but it should work for you.

What I mean by this is that you should pass the indidivual parameters into the stored proc and then use INSERT ... VALUES (@var1, @var2 ) etc.

By passing string based SQL, the calling procedure needs to have intimate knowledge of this particular proc. Not a very good idea. Plus you can't verify that the passed in ReportSQL is a valid sql statements.



-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects