Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-06-14
10
Medium Priority
?
268 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

618 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