Thread: Dynamic Stored Procedure

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?

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

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

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

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]

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?

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]