0

Let's say that I have a transact-SQL query that does something like this:

INSERT INTO Table1 -- Table1 has an identity column for the primary key
(
   Table1Value1,
   Table1Value2
)
VALUES
(
   @Table1Value1, -- SqlParameter
   @Table1Value2 -- SqlParameter
)   
DECLARE @table1ID int = SCOPE_IDENTITY();

INSERT INTO Table2 -- Table2 also has and identity column for the PK
(
   Table1ID -- Must have foreign key to Table1 record
   Table2Value1,
   Table2Value2
)
VALUES
(
    @table1ID,
    @Table2Value1, -- SqlParameter
    @Table2Value2  -- SqlParameter
)

DECLARE @table2ID int = SCOPE_IDENTITY();

INSERT INTO Table3
(
   Table1ID, -- Foreign Key to Table1 record
   Table2ID, -- Foreign Key to Table2 record
   Table3Value1,
   Table3Value2
)
VALUES
(
   @table1ID, -- Foreign Key to Table1 record
   @table2ID, -- Foreign Key to Table2 record
   @Table3Value1, -- SqlParameter
   @Table3Value2 -- SqlParameter
)

Is there a way to make this work? Can you escape the local transact-sql variable names so they're ignored by whatever figures out the location of the parameters? Can you tell SqlClient to use something other than @ to identify parameters? Is there a way to make this work without using a stored proc or having to run subqueries?

Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jim Berg
  • 609
  • 4
  • 7
  • 3
    They're separate things, as long as your script uses different names, parameters and local variables can coexist. Are you having a particular problem with that? – Alejandro Mar 02 '21 at 14:45
  • Are you submitting that as one _batch_ or as separate statements? The latter won't work. – HABO Mar 02 '21 at 14:46
  • @HABO one statement. – Jim Berg Mar 02 '21 at 14:52
  • @Alejandro It says "Must declare scalar variable @table1ID". I changed it to separate the declaration from the assignment and it worked. Thanks for confirming that it should work. – Jim Berg Mar 02 '21 at 15:03
  • Are you declaring and assigning more than variable in a single line? – Charlieface Mar 02 '21 at 15:25
  • @Charlieface The code above is exactly what I did, just redacted some. The parameters I added were just for the values named in the query. – Jim Berg Mar 02 '21 at 16:46
  • 1
    Then I don't get what your issue is. I have never had such a problem. So long as you are not declaring your parameters a *second* time with the same name they should not clash with the parameters. By the way, you should preferably terminate all SQL statements with `;` – Charlieface Mar 02 '21 at 17:17
  • @Charlieface So you used SqlParameter and local Transact SQL variable where you declared them and assigned them in the same statement? I wonder if it's the version of SqlClient I'm using. That would be System.Data.SqlClient 4.8.2. – Jim Berg Mar 02 '21 at 18:45
  • 1
    I cannot replicate. I suggest you go back and check your code again for misspellings or bad syntax. Make sure all statements end with `;` and make sure you are declaring the data types. If you are using a case-sensitive server collation then make sure when you are using the variable you specify it as an exact match on case. – Charlieface Mar 02 '21 at 21:03
  • @Charlieface Thanks for listening. I guess I've just been up too long and didn't notice a missing letter or something. :-) – Jim Berg Mar 02 '21 at 22:08

2 Answers2

0

It had to have been a typo. I changed it back and now it works. The only lesson here is to not work 40 hours in a 48 hour period and that you can mix parameters with transact-sql local variables. :-)

I wouldn't be opposed to deleting this.

Jim Berg
  • 609
  • 4
  • 7
0

You can have client code like this (using C# as the example):

var cmd = new SqlCommand("exec Procedure @Var1, @Var2", connection);
cmd.Parameters.Add("@Var1", SqlDbType.NVarChar, 40).Value = var1Value;
cmd.Parameters.Add("@Var2", SqlDbType.NVarChar, 40).Value = var2Value;  
connection.Open();
cmd.ExecuteNonQuery();

With a procedure including code like this:

-- declare an additional variable in the procedure
DECLARE @VAR3 NVarChar(40) = 'test Value';
SELECT @Var1, @Var2, @Var3;

The trick is if you want to declare a variable in your SQL code or procedure for use only within the procedure or script, don't also provide a parameter for it from the client, because that would be declaring the same variable twice.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • I didn't provide a parameter for it from the client. Does that code you posted really work if you don't separate the assignment of @VAR3 from it's declaration? – Jim Berg Mar 02 '21 at 15:23
  • 1
    You don't need a stored procedure for use local variables. A lone script line the one in the question is also perfectly valid. – Alejandro Mar 02 '21 at 16:45