• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

SQL Server - Pass in Where clause without dynamic sql like this

How can I pass in the where clause to an SP.   I can't use this sort of dynamic sql
EXEC (@SQLCommand)

Because my Linq to Sql (DBML) can evaluate it when I create Linq statements

I need something like this...               ???


create PROCEDURE [dbo].[sp_Get_Prospects]
      
  @WHERE nvarchar(4000)  
      
AS
BEGIN
      

      
      SELECT  ID  FROM Prospects  WHERE  @WHERE  GROUP BY ID

END



any ideas?
0
JElster
Asked:
JElster
  • 5
  • 4
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Dont do that, You are actually violating the best coding standards and opening your sql server for 'SQL Injection'
>sp_Get_Prospects : do not start your sp's with 'sp_' ,sql server thinks the sp call as asystem stored procedure call and will execute if there is a stored procedure with the same name on 'master' database, in case it is not there, it will execute your sp

>@WHERE  : you need to find an alternate way for this. probably passing more parameters to the sp and buiding a dynamic sql with them and executing it with sp_executeSQL.

Gfo thru this link

http://www.sommarskog.se/dynamic_sql.html
0
 
Éric MoreauSenior .Net ConsultantCommented:
0
 
JElsterAuthor Commented:
How do you use it.. this doesn't seem to work.. what's the correct syntax?
thx

 
  declare @stat varchar(150)
  declare @where varchar(100)

  set @stat = 'Select * from Customer where   @where'
  set @where 'name = Smith%'
 
sp_executesql @stat, @where
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Éric MoreauSenior .Net ConsultantCommented:
set @stat = 'Select * from Customer where  '
  set @where = 'name like "Smith%"'
 
sp_executesql @stat + @where
0
 
JElsterAuthor Commented:
I get
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'sp_executesql'.


Don't understand what am doing wrong

thx
0
 
Éric MoreauSenior .Net ConsultantCommented:
add EXEC in front:

EXEC sp_executesql @stat + @where
0
 
JElsterAuthor Commented:
Now get

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '+'.


?????????
0
 
Éric MoreauSenior .Net ConsultantCommented:
can you show your code?
0
 
JElsterAuthor Commented:
declare @stat varchar(150)
  declare @where varchar(100)
   set @stat = 'Select * from customer where  '
  set @where = 'name like "A%"'
    print @stat + @where

 exec sp_executesql @stat + @where
0
 
Éric MoreauSenior .Net ConsultantCommented:
I Just tried this on my side and it is working:

declare @stat nvarchar(150)
declare @where nvarchar(100)
set @stat = 'SELECT * FROM customer WHERE '
set @where = 'Symbol LIKE ''a%'''

SET @stat = @stat + @where    
print @stat

exec sp_executesql @stat
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now