?
Solved

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

Posted on 2013-06-14
10
Medium Priority
?
265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

771 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