October 3, 2002 at 10:57 am
Greetings All,
To begin with, I don't have a lot of experience using Stored Proceedures. I am trying to have a Stored Proceedure to be the Record Source for a form. The SP has 5 input parameters that are based off of selections from another form(which may have values or not). What is the best way using TSql to check each parameter for a value and then make it part of the WHERE Statement.
Is it possible to set a string var and have it build the WHERE Statement?
DECLARE @WhereStr Varchar(100)
SELECT @WhereStr = 'field1 = @param1'
SELECT * FROM Tbl WHERE @WhereStr
I hope this makes sense.
Thanks.
fryere
October 3, 2002 at 11:26 am
You are going to have to build you code dynamically. Here are a couple of links that might help:
http://www.sqlservercentral.com/columnists/rmarda/dynamicsqlversusstaticsqlp1.asp http://www.sqlservercentral.com/columnists/rmarda/whendynamicsqlisuseful.asp
As validation parms, your could do some thing like :
declare @where char(1000)
if @parm1 not null
set @where = 'column_name1 = ' + @parm 1
if @parm2 not null
set @where = @where + 'and column_name2 = ' @parm2
Keep in mind the creation of where statement will need to be syntactically correct.
Also make sure you build some code to make sure someone doesn't pass some additional commands in the where clause like if the where clause was equal to :
@where = 'col1 = 1 + char(13) + 'drop table xyz'
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 3, 2002 at 2:28 pm
Thanks Greg,
I think that I am going to get it to work. The input value that I was testing was an INT and it was giving me problems.
Thanks Again! 🙂
fryere
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply