Passing Paramters to Dynamic SQL

Question

4.00/5 (1 vote)

See more:

Sample SP
My parameter should be like - 'DOB BETWEEN ''1/12/1980'' AND ''1/1/2080''' (Check double quotes)
-- SelectEmp 'DOB BETWEEN ''1/12/1980'' AND ''1/1/2080'''

SQL

ALTER PROCEDURE SelectEmp
	-- Add the parameters for the stored procedure here
	@DOB nvarchar(max)
AS
BEGIN
	SET NOCOUNT ON;
DECLARE @SQL VARCHAR(MAX)
set @Sql = 'select * from emp where '+  @DOB
print @Sql
exec  (@Sql)
END
GO

My .Net Code :-

C#

cn.Open();
               SqlCommand cmd = new SqlCommand("SelectEmp", cn);
               cmd.CommandType = CommandType.StoredProcedure;
               cmd.Parameters.Add("@DOB", SqlDbType.VarChar, 1000);
               cmd.Parameters["@DOB"].Value = "DOB BETWEEN "+"'''"+ "1/12/1980"+"''"+" AND "+ "'''"+ "1/1/2000"+"''"+"";
               SqlDataReader dr;
               dr = cmd.ExecuteReader();
               while (dr.Read())
               {
                   Response.Write("Hi");
               }

I am gettinh Error Incorrect Syntax

Can someone please help me ..

Posted 11-Apr-11 8:48am

gsravisankar

Updated 11-Apr-11 8:55am

TweakBird

v2

Add a Solution

5 solutions

Solution 3

Hi It worked ...Thanks a lot...
I was thinking too much by Seeing

'DOB BETWEEN ''1/12/1980'' AND ''1/1/2080''' (Check double quotes)

Posted 11-Apr-11 9:13am

gsravisankar

Solution 4

When I use the string from your line:

C#

cmd.Parameters["@DOB"].Value = "DOB BETWEEN "+"'''"+ "1/12/1980"+"''"+" AND "+ "'''"+ "1/1/2000"+"''"+"";

and, using code, put it in a TextBox I get:

DOB BETWEEN '''1/12/1980'' AND '''1/1/2000''

Hopefully this will help you to spot the error(s)

Posted 11-Apr-11 9:15am

Henry Minute

Solution 5

Passing a partial SQL statement isn't usually the correct way. This could fail because of several reasons especially when dates are involved. Use 2 separate parameters in your procedure:

SQL

ALTER PROCEDURE SelectEmp
    -- Add the parameters for the stored procedure here
    @DOBstart date,
    @DOBend date
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM EMP WHERE DOB BETWEEN @DOBstart AND @DOBend;
END
GO

Posted 11-Apr-11 9:16am

Wendelius

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)

web works · Accepted Answer · 2011-04-11T09:05:00

Solution 1

Try to modify the line something like this.

cmd.Parameters["@DOB"].Value = "DOB BETWEEN " + "'" + "1/12/1980" + "'" + " AND " + "'" + "1/1/2000" + "'";

Seems like there are extra '/" quotes.

Posted 11-Apr-11 9:05am

web works

web works · Accepted Answer · 2011-04-11T09:05:00

Solution 2

Try to modify the line something like this.

cmd.Parameters["@DOB"].Value = "DOB BETWEEN " + "'" + "1/12/1980" + "'" + " AND " + "'" + "1/1/2000" + "'";

Seems like there are extra '/" quotes.

Posted 11-Apr-11 9:05am

web works