Passing a where clause to a stored proc as a variable
-
Does anyone know how to pass a where clause into a stored procedure?
for example ;
create procedure sp_load_documents_into_folder
@keywordlist nvarchar(4000),
@conditions nvarchar(4000) = NULL
as
declare @tablevar table(keyword varchar(9))
declare @documentnumber int, @foldernumber int, @foldername varchar(50)
set @foldername = 'temp folder' + cast((rand() *100000) as varchar(7))
insert into folders (foldername, createdate) VALUES (@foldername, GETDATE() )
insert into @tablevar select str from iter_charlist_to_table(@keywordlist, DEFAULT)
If @conditions <> Null
BEGIN
DECLARE docinsert CURSOR FAST_FORWARD for
select distinct dn.documentnumber from docnumbers dn (nolock)
inner join DocKeywords dk (nolock) on dk.documentnumber = dn.documentnumber
inner join @tablevar tv on tv.str = dk.keyword_value
where @condition and dn.docdeleted = 0
OPEN docinsert
fetch next from docinsert into @documentnumber
while @@fetch_status =0
BEGIN
insert into folder_items (workspaceid, folderid, documentid) VALUES (@folderid, 0,@docid)
fetch next from docinsert into @documentnumber
END
CLOSE docinsert
DEALLOCATE docinsert
END
ELSE
BEGIN
DECLARE docinsert CURSOR FAST_FORWARD for
select distinct dn.documentnumber from docnumbers dn (nolock)
inner join DocKeywords dk (nolock) on dk.documentnumber = dn.documentnumber
inner join @tablevar tv on tv.str = dk.keyword_value
where dn.docdeleted = 0
OPEN docinsert
fetch next from docinsert into @documentnumber
while @@fetch_status =0
BEGIN
insert into folder_items (workspaceid, folderid, documentid) VALUES (@folderid, 0,@docid)
fetch next from docinsert into @documentnumber
END
CLOSE docinsert
DEALLOCATE docinsert
END
I would want to exec the sp as
exec sp_load_documents_into_folder 'abc,def,abd,dek,gcc',"dn.documentnumber > 50"
which would should insert rows into the folder_items table where the documentnumber is greater than 50.
This does not work and returns an error of incorrect syntax near and on the "Where @condition and ..." line.
Does anyone know what to do to rememdy this?
-
For dynamic SQL you'll need to use the exec command and treat the whole statement as a concatenanted string.
See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ea-ez_05ro.asp
-
Exec robs me of the ability to share variables between the sp and the EXEC.
Am I missing something?
-
What do you mean by "robs me"?
It should be as simple as this:
declare @sqlstring varchar(500)
set @sqlstring = 'update sql code where '+@wherevar+'rest of sql code'
exec (@sqlstring)
-
exec doesnt understand the table variable and wants me to declare it. Its assuming its a local.
-
Try using a temp table instead of a table variable.
I just did a real simple test and it worked.
Here's my quickie code:
select *
into #tmptest
from test_table
declare @sqlstr varchar(200)
set @sqlstr = 'select * from #tmptest'
exec (@sqlstr)
-
It still doesnt like it. Can you try adding the @conditions into your test and see if yuo get the same result. It may be something I am stupidly typing.
Server: Msg 245, Level 16, State 1, Procedure sp_load_workspace_for_publishing, Line 41
Syntax error converting the varchar value 'DECLARE docinsert CURSOR FAST_FORWARD for...
-
Hmmm, that cursor complicates things. Not sure of the error you're getting or why (whould have to see your code).
However, what you might want to do is run the select into (another) temp table and then declare your cursor on select * from the new temp table.
So it would be...
--do code to prep data into first temp table
declare @sqlstr varchar (500)
set @sqlstr = 'select whatever into #tmp2 where'+@where+'whatever else'
exec (@sqlstr)
DECLARE docinsert CURSOR FAST_FORWARD for
select * from #tmp2
open docinsert
---
Totaly untested and theoretically speaking, of course.
The other thing, and truly the recommended approach, would be to refactor this code so that you don't use cursors. If you've read anything around here, you know that cursors are (generally speaking) to be avoided.
Alternatively, unless that "where" clause has to be completely configurable (which since you're doing an update is a big red flag). you might want to look at having a case / if switch with canned SQL rather than using dynamic SQL.
-
If you know of a way to eliminate the need for a cursor in this situation, that would be AWSOME. I havent figured out any way to get around it in this situation where I have to insert multiple rows with some common and some changing values.
I'll hack at it and see if I can figure something out.
Thanks for your Help Pam.
-
Well. I'm trying but this isn't making any sense to me:
select distinct dn.documentnumber from docnumbers dn (nolock)
inner join DocKeywords dk (nolock) on dk.documentnumber = dn.documentnumber
inner join @tablevar tv on tv.str = dk.keyword_value
where @condition and dn.docdeleted = 0
OPEN docinsert
fetch next from docinsert into @documentnumber
while @@fetch_status =0
BEGIN
insert into folder_items (workspaceid, folderid, documentid) VALUES (@folderid, 0,@docid)
Is @documentnumber supposed to be @docid?
Also it looks to me (from names) that it should be values(0,@folderid,@docid).
Give me a clear example and I should be able to come up with a way to eliminate the cursor.
-
My god the timeout on the message board is annoying. You cant even go back to recover what you have typed.
The folder items column list should be (folderid, nodeid, documentnumber)
all references to docid should be documentnumber. I was sloppy when obfuscating the example.
I used what you said in a two stage process instead of one lump and was able to get it to work
; selecting the @tablevar into a temp table and then using it in the EXEC to extract the documentnumbers into another temp table, and then using that second temp table to run the cursor against allowed me to do what I was trying to achieve. I would still be interested to at least in concept know how to replace cursors for this kind of data inserts, if you would be willing to explain.
Thanks for your help Deb!
-
Here's a starting point for you:
create table #tmpfolder_items
(folderid int, nodeid int, documentid int)
create table #tmpdn
(documentnumber int, docdeleted int)
create table #tmpdkw
(documentnumber int,keyword_value varchar(20), docwhere int)
create table #tmptablevar
(str varchar(20))
declare @folderid int
set @folderid = 1
insert into #tmpfolder_items
(folderid, nodeid, documentid)
--VALUES
select distinct
@folderid,
0,
dn.documentnumber
from
#tmpdn dn
INNER JOIN
#tmpdkw dk
on
dk.documentnumber = dn.documentnumber
INNER JOIN
#tmptablevar tv
on
tv.str = dk.keyword_value
where
dk.docwhere > 2
and
dn.docdeleted = 0
drop table #tmpfolder_items
drop table #tmpdn
drop table #tmpdkw
drop table #tmptablevar
-
This error, somewhat erroneously reported as a "Syntax error":
....Server: Msg 245, Level 16, State 1, Procedure sp_load_workspace_for_publishing, Line 41
Syntax error converting the varchar value 'DECLARE docinsert CURSOR FAST_FORWARD for........
Is what you get when you try to concatenate a string value and an integer. In this case, it is not complaining about the syntax of the SQL expression, but rather the values that are in the concatenation operation. The implicit conversion goes from varchar to int when mixing the two types, so you need to make sure that the int is cast to varchar.
select 'a' + 1 will give you the same "syntax error" even thought it is not really a SQL syntax error, but rather an error in the data.
IOW, find the uncast int, and that might fix your problem.
jg
-
5 casts added to that method resulted in no more syntax error and a second working method. Thanks Jeff!
-
Nobody pointed out to this poor soul that
if @conditions Null
won't work...
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply