Stored Procedure with Parameter Error 1

[yan19454]

I created my stored procedure according to this sample. but I do not understanding why it keep give me error for @xquiz is not declarated.

Here is my code.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO





/*. according the due date end user select and ALTER temp table
find the nomatch table in the occh and return the table
*/
ALTER PROCEDURE dbo.test2
@quiz VARCHAR( 25 ),
@debug bit = 0
as
DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)
begin
CREATE TABLE #tmpTrained(
EmplID char(30))
set @sql='select distinct EmplNO from tblCurrentWinTrainingLog where 1=1'

set @sql= @sql+ ' AND quizname = @xquiz '
IF @Debug = 1

BEGIN

PRINT @sql PRINT ''

END
set @sql='Insert into #tmpTrained '+ @sql

exec (@sql)
set @sql='select * from #tmpTrained '

exec (@sql)

SELECT @paramlist = '@xquiz varchar(25)'





EXEC sp_executesql @sql, @paramlist,
@quiz




end
Thx.

http://www.sommarskog.se/dyn-search.html

 

[gmmastros]

Change...
set @sql= @sql+ ' AND quizname = @xquiz '

To...

[tt][blue]
set @sql= @sql+ ' AND quizname = ''' + Replace(@quiz, '''', '''''') + ''''
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[yan19454]

in the

http://www.sommarskog.se/dyn-search.html

They used x@orderid something like parameter list. I am not quite sure how it work. The change you did kind of change the approach he mentioned about. use EXEC sp_executesql

thx.

 

[arrowhouse]

You have declared the input parameter as @quiz VARCHAR( 25 ) when creating the procedure as opposed to @xquiz.

I think that is why when you use @xquiz you get a not declared error.

 

[yan19454]

it tested it, take off the sample x declaration. it did not work. right now I used the stored procedure instead of eXEC sp_executesql .

I think it should be justify for the time for me to have mroe test.

 

[Charlix]

I have a relational database that has several fields in it (color, product, format, type, etc). I want the user to be able to identify products based on the defect, format etc. I can write an SQL statement that gets the products if I specify the color programmatically. For example,

SELECT [Product] FROM [Table1] WHERE [Color] = ‘Red’

This gives the user all the products that have a red color. But, if I use a drop down list (e.g.,a dropdown list named ddColor) and let the user select the color the code doesn’t work. This doesn’t work

SELECT [Product] FROM [Table1] where [Color] = ‘ ddColor.SelectedValue’.

I know this is a trivial problem but I can’t do it. Where do I go to get some help?

 

[yan19454]

SELECT [Product] FROM [Table1] where [Color] = ‘" & ddColor.SelectedValue &"’"

I think here is the vb.net or vb code.