Skip to content
Advertisement

Is it possible you instatiate a variable in SQL server using a select statement with another variable

I am attempting to DECLARE a variable then SET it’s value using a secondary variable which has already had it’s value set.

I have tried:

To no avail. The result of the final SELECT statement is null. The following works:

but I have several tables linked via dependencies and to delete an entry I need to traverse the tables in the correct order pulling the correct Ids. It is likely I will need to do this frequently so I want to reduce the leg work and just enter the text once and the script do the rest.

Is the syntax wrong or is this not possible?

Advertisement

Answer

DECLARE @Type VARCHAR = 'Some unique text';

It seems like you try to configure the variable value to be ‘Some unique text’ but since the type of the variable is VARCHAR(1) then when you set the value the server implicitly CONVERT it to VARCHAR(1) which lead to trunctaing the string and using only the first character

To clarify, using DECLARE @Type VARCHAR without explicitly set the length is translated into ‘DECLARE @Type VARCHAR(1)’

As a result of this issue, your comparing of the value probably result with no rows since you compare “S” and not “Some unique text”. Your sub query is the same as SELECT Id FROM Types WHERE Name = 'S'

Here is a simple illustration of the issue

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement