Discuss "Dynamic Stored Procedure" in the Microsoft SQL Server forum.

jmurrayhead · #1 (**permalink**) 03-25-2008, 11:58 AM

Alright, this is my first stab at a dynamic stored procedure. First off, let me say that I don't get to decide how the database is designed..so that's why the column and table names are horrible.

I have the following:

Code:

DECLARE @sql varchar(255)
SELECT @sql = 'SELECT candidate_last_name, candidate_first_name... ' +
                     'FROM vw_applicationDetail ' +
                     'WHERE application_status_id = ' + @statusCode + ' ' +
                     ...

I have much more but this is where it errors out. @statusCode is an integer data type. The error:

Syntax converting the varchar value ''SELECT candidate_last_name, candidate_first_name... FROM vw_applicationDetail WHERE application_status_id = '' to a column of data type int.

I've tried a variety of ways such as doubling up single quotes around variables and such.

Any pointers?

Lauramc · #2 (**permalink**) 03-25-2008, 12:02 PM

Hey jmh! What is the data type of the @statusCode? If it is not a char or varchar, you have to convert it so it will work in a dynamic procedure. Try CAST(@statuscode as varchar(10)) ... or whatever length you need if 10 is not enough. That should help

jmurrayhead · #3 (**permalink**) 03-25-2008, 12:06 PM

Quote:

Originally Posted by lauramc

Hey jmh! What is the data type of the @statusCode? If it is not a char or varchar, you have to convert it so it will work in a dynamic procedure. Try CAST(@statuscode as varchar(10)) ... or whatever length you need if 10 is not enough. That should help

As stated above

, the data type is int. I also tried as you suggested before, but I get the following error:

Error 403: Invalid operator for data type. Operator equals add, type equals bit.

Wait...that's for the next line...God I feel stupid...All I had to do was the same thing on my second line and it worked

Lauramc · #4 (**permalink**) 03-25-2008, 12:11 PM

Oh good! I am glad you got it sorted. Don't forget that when you execute it you have to do it like so: EXEC(@sql). As a rule I print it while I am testing the string i.e. PRINT @sql. The print will show you what the command looks like so you can tweak it if needed.

jmurrayhead · #5 (**permalink**) 03-25-2008, 12:15 PM

Yeap, I'm still having some issues: Incorrect syntax near 'application_detet'.

Code:

DECLARE @sql varchar(255)
SELECT @sql = 'SELECT candidate_last_name, candidate_first_name... ' +
                     'FROM vw_applicationDetail ' +
                     'WHERE application_status_id = ' + CAST(@statusCode As varchar(10) + ' ' +
                     'AND current_status_ind = ' + CAST(@currentStatus As varchar(10)) + ' '
                     'AND application_deleted_ind = 0 ' +

What is your advise, oh wizardess of SQL?

Your Lord and Master,

JMH

Lauramc · #6 (**permalink**) 03-25-2008, 12:36 PM

You forgot one of the plus signs. Try this:

Code:

SELECT @sql = 'SELECT candidate_last_name, candidate_first_name 
                     FROM vw_applicationDetail 
                     WHERE application_status_id = ' + CAST(@statusCode As varchar(10)) +
                     'AND current_status_ind = ' + CAST(@currentStatus As varchar(10)) + ' ' +
                     'AND application_deleted_ind = 0 '

Also you were missing a closed parentheses. I checked this in query analyzer. It works now

jmurrayhead · #7 (**permalink**) 03-25-2008, 12:39 PM

nope, it's there I just forgot to type it in. I can't copy and paste because I have a dev machine and then this machine

See anything else that might be wrong?

[EDIT]Again...just a typo...that parentheses is actually there

[/EDIT]

Lauramc · #8 (**permalink**) 03-25-2008, 12:49 PM

OK try copying my code into your query analyzer. Another thing.. can you give some sample data for your @statusCode and @currentStatus variables? Are you always getting the same error? If so what does it say?

AOG123 · #9 (**permalink**) 03-25-2008, 12:50 PM

Quote:

Originally Posted by Lauramc

You forgot one of the plus signs. Try this:

Code:

SELECT @sql = 'SELECT candidate_last_name, candidate_first_name 
                     FROM vw_applicationDetail 
                     WHERE application_status_id = ' + CAST(@statusCode As varchar(10)) +
                     'AND current_status_ind = ' + CAST(@currentStatus As varchar(10)) + ' ' +
                     'AND application_deleted_ind = 0 '

Also you were missing a closed parentheses. I checked this in query analyzer. It works now

your missing ' ' + after

(@statusCode As varchar(10)) +

Edit: maybe not,.. you had it earlier but took it out?

jmurrayhead · #10 (**permalink**) 03-25-2008, 12:53 PM

Can't copy and paste...code is on a dev machine.

@statusCode could be 99, @currentStatus is a bit (comes in from .Net app as True or False), @applicationType could be 4.

Again, the error is: Line 1: Incorrect syntax near 'applcation_delet'

[EDIT]@AOG - that was just a typo[/EDIT]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Permissions on Tables, Stored Procedures, etc.	theChris	Microsoft SQL Server	2	03-24-2008 11:49 AM

LinkBacks (?) LinkBack to this Thread: http://www.developerbarn.com/microsoft-sql-server/73-dynamic-stored-procedure.html
Posted By	For	Type	Date
DeveloperBarn Forums - ASP Help, ASP.Net Help, PHP Help, SQL Help, Tutorials, Windows Help	This thread	Refback	04-26-2008 09:36 AM

Discuss "Dynamic Stored Procedure" in the Microsoft SQL Server forum.

Microsoft SQL Server - Microsoft SQL Server is a powerful database engine used for large enterprise applications. Learn how to setup, administer and use SQL Server here.