Dynamic SQL and passed parameters

[tman135]

I'm trying to write a stored proc that will accept a number of inputs from a web page and use them to dynamically build an sql statement eventually to rreturn a result set to drive a report object.

I've gotten the method to work using the execute(@sql) method, but I want to use sp_executesql which should perform better as the database grows since SQL doesn't need to recompile the statement every time.

here's the test shell.... I will eventually have 15 parameters coming in from the web page

CREATE PROCEDURE test_sql
@base_id char(3)
AS
declare @sqlstring nvarchar(1000)
declare @wherestring nvarchar(500)
declare @paramstring nvarchar(500)

set @sqlstring = N'Select * from tbl_buildinginfo where base_id = @base_id1'
set @wherestring =N' where base_id = @base_id1'
set @paramstring = N'@base_id1 char(3)'

set @sqlstring = @sqlstring + @wherestring
print @sqlstring

execute sp_executesql @sqlstring,@paramstring,@base_id1 = @base_id
RETURN

I run the stored proc and the print statement shows me this
Select * from tbl_buildinginfo where base_id = @base_id1
so the sp_executesql method is not seeing the parameter passed to it through the web page.

Any help would be greatly appreciated

 

[tlbroadbent]

I found three problems.[ol][li]You have duplicated the Where clause.
[li]You should use the same variable name (@base_id ) in the SP declaration and in the sp_executesql parameter definition.
[li]The OUTPUT declaration on the parameter is missing.[/ol]Correct the script as follows.

CREATE PROCEDURE test_sql
@base_id char(3)
AS
declare @sqlstring nvarchar(1000)
declare @wherestring nvarchar(500)
declare @paramstring nvarchar(500)

set @sqlstring = N'Select * from tbl_buildinginfo'
set @wherestring =N' where base_id = @base_id'
set @paramstring = N'@base_id varchar(30) Output'

set @sqlstring = @sqlstring + @wherestring
print @sqlstring

execute sp_executesql @sqlstring, @paramstring, @base_id=@base_id
RETURN Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.