?
Solved

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

Posted on 2013-06-14
10
Medium Priority
?
269 Views
Last Modified: 2013-06-17
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
Comment
Question by:JElster
  • 5
  • 4
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39248888
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
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 2000 total points
ID: 39251472
0
 
LVL 1

Author Comment

by:JElster
ID: 39251674
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39251693
set @stat = 'Select * from Customer where  '
  set @where = 'name like "Smith%"'
 
sp_executesql @stat + @where
0
 
LVL 1

Author Comment

by:JElster
ID: 39251817
I get
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'sp_executesql'.


Don't understand what am doing wrong

thx
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39251837
add EXEC in front:

EXEC sp_executesql @stat + @where
0
 
LVL 1

Author Comment

by:JElster
ID: 39251842
Now get

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


?????????
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39252076
can you show your code?
0
 
LVL 1

Author Comment

by:JElster
ID: 39252113
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39252720
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question