resolving - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

resolving

Author

Topic

Beginer2012
Starting Member

45 Posts

Posted - 2012-04-30 : 12:47:01

----------------------
| URGENT..PLEASE HELP |
----------------------

Hello,
I have a table that have a column that store varnames:
@var1
@var2
@var2
.
.
.
@varn

DELCARE @var7
SET @var7=20

SET @sql =(SELECT 'INSERT INTO TEST (Counts,Code) VALUES('+ CountName + ',' + CONVERT(VARCHAR(100),grp)+ ')' FROM Data
WHERE RowNumber = 7)

The CountName is resolving to @var7 and not to the actual value 20 Can some body help me with a way to resolve this @var7 to 20.
I need to use this for a dynamic sql stuff.
Thank you

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-04-30 : 13:57:02

www.sommarskog.se/dynamic_sql.html

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Beginer2012
Starting Member

45 Posts

Posted - 2012-04-30 : 20:47:50

Sorry ! That article does not solve my problem.

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-01 : 06:49:58

From the above code I can't make out how "CountName is resolving to @var7".....
Please post the relevant code which shows how CountName is related to @var7 and how it is resolving CountName = @var7.

Without looking at that we won't be able to provide a solution for this requirement.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Beginer2012
Starting Member

45 Posts

Posted - 2012-05-01 : 08:51:50

Hello,
I have the table Data as:
CountName
----------
@Var1
@Var2
@Var3
@Var4
.
.
.
@Var1

Now...
DECLARE @Var1,@Var2....@Varn INT

SET @Var7 = Select count(*) from mytable where a !=0

DECLARE @Count INT
SET @Count=10

DECLARE @Sql NVARCHAR (200)

WHILE (@Count > 0)
BEGIN
SET @sql =(SELECT 'INSERT INTO TEST (Counts,Code) VALUES('+CountName + ',' + CONVERT(VARCHAR(100),grp)+ ')' FROM Data WHERE RowNumber = @Count)
END

when I print @sql it prints :
INSERT INTO TEST (Counts,Code) VALUES(10,1400)
But when I try to execute the @sql it does not resolve @Var7 to 10. It keeps @Var7 as text.

I need a way to resolve the @var7 the moment I select it from the table.

Thank you

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-02 : 00:47:16

quote:
Originally posted by Beginer2012

Hello,
I have the table Data as:
CountName
----------
@Var1
@Var2
@Var3
@Var4
.
.
.
@Var1

Now...
DECLARE @Var1,@Var2....@Varn INT

SET @Var7 = Select count(*) from mytable where a !=0

DECLARE @Count INT
SET @Count=10

DECLARE @Sql NVARCHAR (200)

WHILE (@Count > 0)
BEGIN
SET @sql =(SELECT 'INSERT INTO TEST (Counts,Code) VALUES('+CountName + ',' + CONVERT(VARCHAR(100),grp)+ ')' FROM Data WHERE RowNumber = @Count)
END

when I print @sql it prints :
INSERT INTO TEST (Counts,Code) VALUES(10,1400)
But when I try to execute the @sql it does not resolve @Var7 to 10. It keeps @Var7 as text.

I need a way to resolve the @var7 the moment I select it from the table.

Thank you

I seriously think that I must have gone blind

I'll tell you what I am not able to understand from your query. You'll have to explain it to me in order for me to find a solution. Its as simple as that. First I'll quote the parts I understand:

DECLARE @Var1,@Var2....@Varn INT


SET @Var7 = Select count(*) from mytable where a !=0


DECLARE @Count INT 
SET @Count=10

DECLARE @Sql NVARCHAR (200)

From the above code I am able to understand that you are declaring temporary variables - @Var1,@Var2....@Varn, @Count, @Sql and you are setting values to those variables.
Now I'll quote the part that I do not understand:

WHILE (@Count > 0)
BEGIN
SET @sql =(SELECT 'INSERT INTO TEST (Counts,Code) VALUES('+CountName + ',' + CONVERT(VARCHAR(100),grp)+ ')' FROM Data WHERE RowNumber = @Count) 
END

Looking at the above code, I am not able to understand, where you are getting "CountName" from....is the query running as part of code of your front end application??...Are you passing "CountName" from there??
And, you say that you are printing the above query....I can't see "@var7" anywhere in the above query, if it is not present in the query then how can the compiler resolve it to anything???

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Beginer2012
Starting Member

45 Posts

Posted - 2012-05-02 : 08:57:39

CountName is a Column in a table called Data.
One of the rows in this column stores the "@Var7" as a text.
This column stores variable names with the @ sign.

When the loop kicks in, it select @var7 as text and replace in @sql.
but @var does not get resolved to 10 or what ever value it was assigned. it stays as @var7.

When I print @sql it @var7 to a value or int. But when I executed it does not resolve it stays @var7.

I need a trick to resolve this @var7 on the spot when I selected from the table data.

Any questions let me know.
Thank you

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-03 : 03:41:51

Since you have not felt the need to provide DDL, Sample Data....I made an attempt to create some for my self and test your query against that data. If this data doesn't do justice to you requirement then I suggest that you post some yourself in the format that I am posting. Here is my attempt:


--Creating two Sample Tables

Create Table Ex
(RowNumber int Identity(1,1),
 CountName varchar(10),
 Status varchar(10) )
 
Create Table Ex1
(RowNumber int Identity(1,1),
 CountName varchar(10),
 Status varchar(10) )


--Inserting Sample Data into First Table

Insert Into Ex
Select '@var1', 'Checked'
Union ALL
Select '@var2', 'Checked'
Union ALL
Select '@var3', 'Checked'
Union ALL
Select '@var4', 'Checked'
Union ALL
Select '@var5', 'Checked'
Union ALL
Select '@var6', 'Checked'
Union ALL
Select '@var7', 'Checked'
Union ALL
Select '@var8', 'Checked'
Union ALL
Select '@var9', 'Checked'
Union ALL
Select '@var10', 'Checked'


--Testing Code with Print

DECLARE @Count INT
Declare @var10 int
Declare @sql varchar(max)
Select @var10 = COUNT(*) From Ex Where Rownumber <> 0
Set @Count = 10
SET @sql =(SELECT 'INSERT INTO Ex1 (CountName, Status) VALUES('+CountName+','+status+')'
FROM Ex WHERE ROWNUMBER = @Count)
Print @sql
--Exec (@sql)

It does not print the value of '@var10' even when you print '@sql'.
This is what it prints:


INSERT INTO Ex1 (CountName, Status) VALUES(@var10,Checked)

The reason behind this is that the query that @sql is holding is simply a select you do on your table where you Select Countname and some other field from your table.
If CountName stores '@var10' then it will Print '@var10' and not the value of '@var10'.

I don't know what you are trying to do with the code. But I fixed it up and got it working as follows:


--Fixed Code with Print and Exec

DECLARE @Count INT
Declare @var10 int
Declare @sql varchar(max)
Select @var10 = COUNT(*) From Ex Where Rownumber <> 0
Set @Count = 10
SET @sql =(SELECT 'Declare @var10 varchar(30)
Set @var10 = ''Your Input''
INSERT INTO Ex1 (CountName, Status) VALUES('+CountName+','''+status+''')'
FROM Ex WHERE ROWNUMBER = @Count)
Print @sql
Exec (@sql)

Hope this works.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Beginer2012
Starting Member

45 Posts

Posted - 2012-05-03 : 12:06:38

Thank you for your help. That is exacly what I want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources