Issue with Dynamic SP

Question

Post reply

Issue with Dynamic SP

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

You have to name your parameter or it will assume it the first parameter is being entered.

Conn.Execute("search_orders_1 @xGroup_Name = '" & request.form("Group_Name") & "'")

noeld SSC Guru Points: 96590 More actions · Answer 2

besides the above correction I believe that to use the connection object to execute the procedure is not the best way I would recommend you use the command object instead

* Noel

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

No problem!

Although it does seem so, I'm not totally against the use of dynamic sql. There are cases with very complex searches where you are better off with dynamic sql, but these are few.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 4

I am wondering if the SP works at all with the syntax that it is written with currently , I believe the variables should not be included inside the apostophies like it should be

IF @id IS NOT NULL

SELECT @sql = @sql + ' AND d.id = @xid'

should be

IF @id IS NOT NULL

SELECT @sql=@sql+ ' AND d.id ='+ ''+ @xid + ''

Otherwise the variables may not get substituted with the passed in values as even the variable will be considered as part of the string, and if the variable is of int or numeric type then it can be written as

SELECT @sql=@sql+ ' AND d.id ='+ @xid

I don't know if I am missing anything here.

Thanks

Prasad Bhogadi
www.inforaise.com