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

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:

DECLARE @Type VARCHAR = 'Some unique text';
DECLARE @TypeId UNIQUEIDENTIFIER; 

SET @TypeId = 
(
    SELECT Id
    FROM Types
    WHERE Name = @Type
)

select @TypeId

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

DECLARE @TypeId UNIQUEIDENTIFIER; 

SET @TypeId = 
(
    SELECT Id
    FROM Types
    WHERE Name = 'Some unique text'
)

select @TypeId

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?

CodePudding user response：

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

DECLARE @Type VARCHAR = 'Some unique text';
SELECT @Type
GO -- result is "S" and not "Some unique text"

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

------------ DDL DML: Creating sample table with some data
DROP TABLE IF EXISTS t1, t2
GO

CREATE TABLE t1 (
    ID int, [Name] NVARCHAR(100)
)
GO

INSERT t1 (ID, [Name]) Values 
(1, 'Mobile '),
(2, 'TV     '),
(3, 'Display')
GO

----------- Using VARCHAR without length returns nothing

DECLARE @Type VARCHAR = 'Mobile';
SELECT @Type

DECLARE @TypeId INT; 
SET @TypeId = 
(
    SELECT Id FROM t1 WHERE Name = @Type
)
select @TypeId


----------- Using VARCHAR(100) works great
DECLARE @Type VARCHAR(100) = 'Mobile';
SELECT @Type

DECLARE @TypeId INT; 
SET @TypeId = 
(
    SELECT Id FROM t1 WHERE Name = @Type
)
select @TypeId