Dynamic SQL statement

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008

Hello friends,
I'm trying to create Dynamic SQL statement in Stored Procedure, here is my following code

ALTER PROC usp_GetJobCheckListDetails_sel
(
	@strSEARCH VARCHAR(500)
)
AS
BEGIN
	--DECLARE @SQL AS VARCHAR(800)
	--SELECT @SQL = 'SELECT J.CheckListId, JobNo, DepartmentId, CheckListName, ControlValue, jobtype FROM tbl_JobCheckList J
	--INNER JOIN tbl_DepartmentCheckList D ON D.CheckListId=J.CheckListId
	--WHERE ' + @strSEARCH + ''
	--EXEC @SQL
	EXEC('SELECT J.CheckListId, JobNo, DepartmentId, CheckListName, ControlValue, jobtype FROM tbl_JobCheckList J
	INNER JOIN tbl_DepartmentCheckList D ON D.CheckListId=J.CheckListId
	WHERE ' + @strSEARCH + '')
END

EXEC usp_GetJobCheckListDetails_sel  'DepartmentId IN (2,4,5) AND jobtype = 5'

My quires are:
1. When i used the comment part as part of SP, then i get error saying not valid identifier. So i commented that part.
2. When i use later part, it does not give any result.

Any idea, where i going wrong?

Thanks in advance

Posted 17-Apr-11 21:34pm

dhage.prashant01

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Toniyo Jackson · Answer 1 · 2011-04-17T21:43:00

Solution 1

Pass two parameters(DepartmentId, jobtype). Then modify the procedure based on that.

ALTER PROC usp_GetJobCheckListDetails_sel
(
	@deptId VARCHAR(500),
        @jobType VARCHAR(50)
)
AS
BEGIN
	
	SELECT J.CheckListId, JobNo, DepartmentId, CheckListName, ControlValue, jobtype FROM tbl_JobCheckList J
	INNER JOIN tbl_DepartmentCheckList D ON D.CheckListId=J.CheckListId
	WHERE DepartmentId IN (@deptId) AND jobtype = @jobType  
	
END

Then call the Sp like below.

EXEC usp_GetJobCheckListDetails_sel  ''2','4','5'','5'

This is the write way.

Posted 17-Apr-11 21:43pm

Toniyo Jackson

Updated 17-Apr-11 23:01pm

v3

Comments

dhage.prashant01 18-Apr-11 4:26am

Nice idea, but any way to do by dynamic query?

Manfred Rudolf Bihy 18-Apr-11 4:59am

Dynamic queries are subject to SQL injection and should be avoided. It really is better to do it in this way!

dhage.prashant01 18-Apr-11 4:55am

tell me, My DepartmentId is integer how can we type cast @deptId to integer??

Toniyo Jackson 18-Apr-11 5:01am

Keep it in VARCHAR only. Because you need to pass multiple department id. So pass the id like this.
EXEC usp_GetJobCheckListDetails_sel ''2','4','5'','5'.
Use single quotes for each id.

Manfred Rudolf Bihy 18-Apr-11 5:00am

Better approach than using dynamic SQL, my 5+!
Dynamic SQL is vulnerable to SQL injection and should be avoided. :thumbsup:

dhage.prashant01 18-Apr-11 5:06am

EXEC usp_GetJobCheckListDetails_sel ''2','4','5'','5'

i tried above, it gives error saying:
Incorrect syntax near '2'.

dhage.prashant01 18-Apr-11 5:45am

No ans, for this??

Mahendra.p25 · Answer 2 · 2011-04-17T21:55:00

Solution 2

try that in the Following way

SQL

DECLARE @Query  VARCHAR(800)
DECLARE @strSEARCH Varchar(50)
set @strSEARCH = 'ProductId = 1'
    set @Query = 'Select * from Products
    WHERE ' + @strSEARCH + ''
    EXEC (@Query)

Posted 17-Apr-11 21:55pm

Mahendra.p25

Comments

dhage.prashant01 18-Apr-11 4:26am

I think, i have tried this

DECLARE @SQL AS VARCHAR(800)
SELECT @SQL = 'SELECT J.CheckListId, JobNo, DepartmentId, CheckListName, ControlValue, jobtype FROM tbl_JobCheckList J
INNER JOIN tbl_DepartmentCheckList D ON D.CheckListId=J.CheckListId
WHERE ' + @strSEARCH + ''
EXEC @SQL

it gives error to me

Manfred Rudolf Bihy 18-Apr-11 4:58am

Look again:
mahen said Set @SQL = and you said SELECT @SQL =
there really is a difference!

Manfred Rudolf Bihy 18-Apr-11 4:58am

My 5!

Mahendra.p25 18-Apr-11 5:07am

Thanks

mhwasim · Answer 3 · 2011-05-18T02:57:00

The problem is only that you are calling EXEC without round brackets.

Try this

SQL

DECLARE @SQL AS VARCHAR(800)
    SELECT @SQL = 'SELECT J.CheckListId, JobNo, DepartmentId, CheckListName, ControlValue, jobtype FROM tbl_JobCheckList J
    INNER JOIN tbl_DepartmentCheckList D ON D.CheckListId=J.CheckListId
    WHERE ' + @strSEARCH + ''
    EXEC(@SQL)

Its perfectly working fine for me...

Thanks