Dynamically Declare Variable in T-SQL

[snakehips2000]

Can someone please tell me why this doesn't work?

declare @sql varchar(100)
SET @sql='DECLARE @myvar int'
EXEC(@sql)
SET @myvar=5
SELECT @myvar

Thanks

 

[markros]

You would need to use sp_ExecuteSQL procedure and you only would be able to get the value of the variable defined in the script (e.g. such declare would not work).

 

[bborissov]

That didn't work because
Dynamic SQL is executed in separate batch and all local variables are released after the batch finished.
BTW why you need Dynamic SQL to declare variable?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[snakehips2000]

I'd already tried tried sp_ExecuteSQL to no avail.

If it's possible to reference a cursor declared dynamically within the same script and return 5 records (as below) ...

declare @sql varchar(200)
declare @my_id int

set @sql='DECLARE my_cur CURSOR FOR SELECT TOP 5 [field name] FROM [table name]'
EXEC sp_ExecuteSQL @sql

OPEN my_cur
FETCH NEXT FROM my_cur INTO @my_id
WHILE @@FETCH_STATUS=0
    BEGIN
        SELECT @my_id
        FETCH NEXT FROM my_cur INTO @my_id
    END
CLOSE my_cur
DEALLOCATE my_cur

... why isn't it possible to declare and reference an even simpler variable as in ...

declare @sql varchar(100)
SET @sql='DECLARE @myvar int'
EXEC sp_ExecuteSQL sql
SET @myvar=5
SELECT @myvar

???
Thanks

 

[bborissov]

What you try to do?
Maybe there is a better way to do this w/o involving any CURSORs and Dynamic variables?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[snakehips2000]

OK, here's what I'm trying to do:

I'm writing a script which will allow the contents of ANY two tables to be compared (assuming they both have identical column names & datatypes).

The aim is to examine the contents of Col_1 from Table_1 and check to see if any of the contained values differ from those in Col_1 of Table_2. If there is a differing value in any row, I have to copy that entire row to a separate results table. The process is repeated for Columns_2 to Column_x until it reaches the last column.

For example , at a simple level, assume the two tables chosen each have three columns and no unique identifier column.

Table_1:
row 1: John, Smith, Male
row 2: Fred, Smith, Male
row 3: Charlie, Smith, Male

Table_2:
row 1: John, Smith, Male
row 2: Peter, Smith, Male
row 3: Charlie, Smith, Male

I'm looking for the ability to loop through each column of Table_1, starting at col_1, identifying that it contains John, Fred and Charlie. I then look at Col_1 of Table_2 and discover that my dynamically-built "NOT IN('John', 'Fred', 'Charlie')" clause returns 'Peter'. I then have to take the entire contents of that row where 'Peter' appears and copy it's contents to another results table.

To do this, I decided that the best way would be to use two cursors (one nested inside the other) and to dynamically build the string which both creates the WHERE clause for the nested cursor and (because the tables may have an as yet undetermined number of columns) declare each cursor variable dynamically as the cursor itself is created. This is where my idea has fallen down.

I initially looked at the possibilities of using the EXCEPT and INTERSECT keywords with simple queries but these won't do the job I need.

Thanks.

 

[bborissov]

But what if you have this:
[tt]
Table_1:
row 1: John, Smith, Male

Table_2:
row 1: John, Dow, FeMale
[/tt]
???
Than comparing Column1 will return nothing?
Maybe it is best to compare the whole row?


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.