T-SQL Stored procedure, declaring variables within the exec

Question

I'm trying to create a stored procedure using T-SQL to insert variables into a table. Using the method that I know of, you declare the variables in the creation of the stored procedure, but I believe it's possible to declare the variables as part of the exec statement to make the insert dynamic. I've googled and googled and googled and cannot find anything that supports this.

Here is my example code as it stands with static variables:

create procedure spInsert as
    declare @insertValueOne varchar(5) = 'Test1'
    declare @insertValueTwo varchar(5) = 'Test2'
    declare @insertValueThree varchar(5) = 'Test3'
    declare @insertValueFour varchar(5) = 'Test4'

begin

insert into testTable
(ValueOne, ValueTwo, ValueThree, ValueFour)
values
(@insertValueOne, @insertValueTwo, @insertValueThree, @insertValueFour)

end

exec spInsert

What I'm trying to achieve is a situation where I can can use an execution script like this for example:

exec spInsert('Test1', 'Test2', 'Test3', 'Test4')

Instead of creating static variables within the procedure. Allowing the user to execute the stored procedure without amending the contents of it.

Does this make sense? Any ideas?

the parentheses after exec spInsert? would it just insert my variables in the sequential order that they were declared in? thx — roastbeeef
– roastbeeef, Commented Jan 13, 2016 at 17:08

Lukasz Szozda · Accepted Answer · 2016-01-13 17:20:53Z

1

Use aguments with default value instead of local variables:

create procedure dbo.spInsert
   @insertValueOne   varchar(5) = 'Test1'
  ,@insertValueTwo   varchar(5) = 'Test2'
  ,@insertValueThree varchar(5) = 'Test3'
  ,@insertValueFour  varchar(5) = 'Test4'
AS
BEGIN
  -- You can still use local variables
  -- DECLARE @my_local_variable = UPPER(@insertValueOne); 

  INSERT INTO testTable(ValueOne, ValueTwo, ValueThree, ValueFour)
  VALUES (@insertValueOne, @insertValueTwo, @insertValueThree, @insertValueFour)
END;

Call:

-- named parameters (good practice, self-documenting)
EXEC spInsert @insertValueOne = 'Test1', @insertValueTwo = 'BBB';
-- positional parameters(order is crucial)
EXEC spInsert 'Test1', 'Test2', 'Test3', 'Test4'

LiveDemo

Naming user defined stored procedure with sp is not best practice.

I think this looks like my solution

No it isn't your stored procedure does not accept any arguments.

create procedure spInsert as
    declare @insertValueOne varchar(5) = 'Test1'
    declare @insertValueTwo varchar(5) = 'Test2'
    declare @insertValueThree varchar(5) = 'Test3'
    declare @insertValueFour varchar(5) = 'Test4'

begin

insert into testTable
(ValueOne, ValueTwo, ValueThree, ValueFour)
values
(@insertValueOne, @insertValueTwo, @insertValueThree, @insertValueFour)

end

edited Jan 13, 2016 at 17:20

answered Jan 13, 2016 at 17:07

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

roastbeeef Over a year ago

I think this looks like my solution, however, I would like some variables static and some as dynamic arguments - is this possible? would the default values remain as 'Test1' etc until I overwrite them? thanks for the comment re: best practice, I'll rename them to something else. Also, when you say positional parameters is this just over writing the preset variables in the order that they were created?

Lukasz Szozda Over a year ago

@roastbeeef Play with LiveDemo. Of course you can add local variables to your stored procedure. With positional parameters order is very important.

David Rushton · Accepted Answer · 2016-01-13 17:16:35Z

You are nearly there. Your code contains a few errors. My example only demonstrates a fraction of what can be achieved with stored procedures. See Microsoft's MSDN help docs on procedures for more.

The example uses a temp procedure (the hash in front of the name makes it temp). But the principles apply to regular SPs.

SP

/* Declares a temp SP for testing.
 * The SP has two parameters, each with a default value.
 */
CREATE PROCEDURE #TempExample
    (
        @ValueOne    VARCHAR(50) = 'Default Value One',
        @ValueTwo    VARCHAR(50) = 'Default Value Two'
    )
AS
SET NOCOUNT ON;
BEGIN

    SELECT 
        @ValueOne    AS ReturnedValueOne,
        @ValueTwo    AS ReturnedValueTwo    
END
GO

I've included the SET NOCOUNT ON statement. MSDN recommends this in the section on best practice.

Use the SET NOCOUNT ON statement as the first statement in the body of the procedure. That is, place it just after the AS keyword. This turns off messages that SQL Server sends back to the client after any SELECT, INSERT, UPDATE, MERGE, and DELETE statements are executed. Overall performance of the database and application is improved by eliminating this unnecessary network overhead.

In the example I've given each parameter a default value, but this is optional. Below shows how you can call this SP using variables and hard coded values.

Example Call

/* We can declare varaiabels outside the SP
 * to pass in values.
 */
DECLARE @ParamOne    VARCHAR(50) = 'Passed Value One';

/* Calling the SP with params.
 * You can use variables or hard  coded values.
 */
EXECUTE #TempExample @ParamOne, 'Passed Value Two';

user3583912 · Accepted Answer · 2016-01-13 17:09:05Z

0

I hope this helps.. remove parentheses

exec spInsert 'Test1', 'Test2', 'Test3', 'Test4'

answered Jan 13, 2016 at 17:09

user3583912

1,3221 gold badge17 silver badges23 bronze badges

Collectives™ on Stack Overflow

T-SQL Stored procedure, declaring variables within the exec

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related