Stored Proc Question: Use ad hoc Where Clause
in [Programming SQL]
|
From: Ray on 25 Jun 2010 16:58 Ive got a question on how to implement the following: I Have an sp_ that accepts two parameters. Here's essentially what the sp looks like: /****************************************************************************************************** Create Procedure sp_SearchResults @Status_ID Int, @Location_ID Int As Declare @SqlString varchar(100); /** Check values passed in to sp and based off these construct @sqlString to use in Where clause **/ If @Status_ID <> 0 Begin Select @SqlString = 'Where Mytable.Status_ID = '+@Status_ID End IF @Location_ID <> 0 and @Status_ID <>0 Begin Select @SqlString = @SqlSring+' AND Mytable.Location_ID = '+@Location_ID End If @Location_ID <> 0 and @Status_ID = 0 Begin Select @SqlString = 'Where Mytable.Location_ID = '+@Location_ID End Exec('Insert Into Results Table (Field1,Field2,Field3,Field4) Select Field1,Field2,'+@Status_ID+','+@Location_ID+ 'From MyBigTable '+@SqlString) ******************************************************************************************************/ I'm having a difficult time having this fly. What am I doing wrong? Is there an cleaner-smoother solution of creating an ad hoc Where clause based on para Much appreciation -Ray
From: Erland Sommarskog on 25 Jun 2010 18:32 Ray (judyscout(a)yahoo.com) writes: > > Ive got a question on how to implement the following: > > I Have an sp_ that accepts two parameters. Don't use the sp_ prefix when you name your stored procedures. This is reserved for system procedures, and SQL Server will first look for them in the master database. > Here's essentially what the sp looks like: I have an article on my web site, or more exactly two on the same theme: http://www.sommarskog.se/dyn-search.html that discusses these kind of searches. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: ray on 26 Jun 2010 14:39 Erland, I can't thankyou enough. That was the :"magic bullet!" Thanks again. -Ray "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9DA35705A71AYazorman(a)127.0.0.1... > Ray (judyscout(a)yahoo.com) writes: >> >> Ive got a question on how to implement the following: >> >> I Have an sp_ that accepts two parameters. > > Don't use the sp_ prefix when you name your stored procedures. This > is reserved for system procedures, and SQL Server will first look > for them in the master database. > > >> Here's essentially what the sp looks like: > > I have an article on my web site, or more exactly two on the same > theme: http://www.sommarskog.se/dyn-search.html that discusses these > kind of searches. > > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
|
Pages: 1 Prev: Help with upgrade from SQL 2000 sp3 to 2005 Next: eliminating duplicates |