CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Esteban
Esteban

Reputation: 67

Stored procedure with select as parameter

I just want to ask a little question: I have a stored procedure called SP_CARGA_NUEVOS.

To use this stored procedure, I need a string in this format: 'AD18010611'.

The last 4 digits of the string are the current month and day, so instead of changing the value manually I wrote this little query:

SELECT 
    'AD1801' +  
    CONVERT(VARCHAR(2), GETDATE(), 101) + 
    CONVERT(VARCHAR(2), GETDATE(), 103)

Which returns AD18010611

But when I try to use the stored procedure, I get an error:

Procedure or function 'SP_CARGA_NUEVOS' expects parameter '@COMENTARIO', which was not supplied.

My question: is there a way to send a select as a parameter to a stored procedure?

Thanks in advance

Edit 1:

I am calling the SP like this

Exec dbo.SP_CARGA_NUEVOS select 'AD1801'+ convert(varchar(2), GETDATE(), 101) + 
convert(varchar(2), GETDATE(), 103)

Which is not working.

The right way is:

Exec dbo.SP_CARGA_NUEVOS 'AD18010611'

Upvotes: 3

Views: 2093

Answers (2)

Laughing Vergil
Laughing Vergil

Reputation: 3756

You cannot call a stored procedure with a query output directly, as you are trying to do here. Note that in the description of the EXECUTE call's syntax, parameter usage is defined as:

    [ [ @parameter = ] { value   
                       | @variable [ OUTPUT ]   
                       | [ DEFAULT ]   
                       }

Translating, this says you can use a constant value, a variable, or accept the default value (if there is one). Accepting the output of a query is not an option.

Upvotes: 0

marc_s
marc_s

Reputation: 754488

You cannot have expressions or selects in the middle of the EXEC - you need to first select the value you want to use into a variable, and then call your stored procedure - like this:

DECLARE @Input VARCHAR(20);

SELECT @Input = 'AD1801' +
                CONVERT(VARCHAR(2), GETDATE(), 101) + 
                CONVERT(VARCHAR(2), GETDATE(), 103)

EXEC dbo.SP_CARGA_NUEVOS @Input

Upvotes: 2

Related Questions