Solved

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

Posted on 2013-06-14
10
263 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 500 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

738 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