Dynamic Search stored procedure
-
I created stored procedure according to
http://www.sommarskog.se/dyn-search.html
this link. I do not know why it keep me error for @xquiz id not declared error.
I do not see any difference from the sample.
/*. 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 nVARCHAR( 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 nvarchar(25)'
EXEC sp_executesql @sql, @paramlist,
@quiz
end
Thx.
-
-- Comments Added
ALTER PROCEDURE dbo.test2
@quiz nVARCHAR( 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
-- This is what is causing the error
-- exec (@sql)
-- Exec cannot execute the sql without the variable
-- a print statement here would be equivelent to
/*
select distinct EmplNO from tblCurrentWinTrainingLog where 1=1 AND quizname = @xquiz
*/
-- Execute by creating paramlist
SELECT @paramlist = '@xquiz nvarchar(25)'
EXEC sp_executesql @sql, @paramlist,
@quiz
set @sql='select * from #tmpTrained '
-- Execute return but you would not need Dynamic sql here
exec (@sql)
-- this would be better here.
select * from #tmpTrained
end
-
then , how to fix ? Thx.:
-
You keep posting this code, which does not make much sense. Try starting with something like:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.TestProc
@quiz NVARCHAR(25)
AS
SET NOCOUNT ON
CREATE TABLE #tmpTrained
(EmplID CHAR(30) COLLATE DATABASE_DEFAULT NOT NULL)
INSERT #tmpTrained
SELECT DISTINCT EmplNo
FROM tblCurrentWinTrainingLog
WHERE quizname = @quiz
-- I presume you want to do something with #tmpTrained here.
-- I suspect a derived table would be a better option.
-- #tmpTrained goes out of scope as the procedure ends
GO
-
you are right. I gave up. I just used stored procedure. it work. thx.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply