CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Corovei Andrei
Corovei Andrei

Reputation: 1668

Finding if a variabile is defined

I have to implement a module that expects as parameter some variable declarations. I have to find a way of validating those variables, so that I didn't predefined them.

Example

declare @var int
set @var = 1

if isdeclared(@var) == 0
begin
    declare @var int
    print @var
end
print @var

Upvotes: 1

Views: 375

Answers (3)

Elias Hossain
Elias Hossain

Reputation: 4469

You can't Complie or Run Sql statement in Sql Server Management Studio.

Say, in JS you can compile/execute code block having a undeclared variable and can check if the variable is declare or not using typedef but in Sql Server it's not possible till to current version. Thanks for your time.

Upvotes: 1

MatBailie
MatBailie

Reputation: 86775

I'm not completely certain what you mean by module, but if you create a stored procedure you can be certain of what variables exist in that scope.

CREATE PROCEDURE myStoredProcedure (@var1 AS INT = NULL, @var2 AS INT = NULL)
AS
BEGIN

  DECLARE @var3 INT

  IF (@var1 IS NULL)
    SET @var3 = 'Nothing was passed in for @var1'

  IF (@var1 IS NULL AND @var2 IS NULL)
    SET @var3 = @var3 + ', '

  IF (@var2 IS NULL)
    SET @var3 = @var3 + 'Nothing was passed in for @var2'

  SELECT
    @var1, @var2, @var3

END

Even though the parameters are optional, they're still decalred and have default values (NULL in this case). So, you don't need to check declarations, only the values.

Upvotes: 2

Mikael Eriksson
Mikael Eriksson

Reputation: 139000

SQL Server parses the code for variable declarations before it executes the code.

This code will work just fine

if 1 = 0
begin
    declare @var int
    print @var
end
print @var

The variable is declare even if the code that declares the variable is never executed.

What you might want to do is to test if the variable is assigned or not instead.

if 1 = 0
begin
    declare @var int
    set @var = 1
    print @var
end

if @var is null 
begin
  set @var = 2
end

print @var

Upvotes: 4

Related Questions