URGENT: Getting value dynamically from a table

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

SQL Server 2000 Forums

SQL Server Development (2000)

URGENT: Getting value dynamically from a table

Author

Topic

amjath1
Starting Member

6 Posts

Posted - 2007-08-29 : 14:32:55

I want to write a function or that sort to return a value for a table and column. As the table and column could be anything it should be dynamic.
I wrote something like the following one.

----------------------------------------------------------------------
DECLARE @value1 VARCHAR(1000)
EXEC ('SELECT @value1 = CAST(mno AS VARCHAR(1000)) FROM servtest WHERE rowno = 1')
PRINT @value1
----------------------------------------------------------------------

But I get the following error.

--------------------------------------------------
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@value1'.
--------------------------------------------------

As you could see the @value1 is already declared. Any thoughts?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 14:56:24

YOU DON'T NEED DYNAMIC SQL FOR THIS!

DECLARE @value1 VARCHAR(1000)
SELECT @value1 = CAST(mno AS VARCHAR(1000)) FROM servtest WHERE rowno = 1
PRINT @value1

E 12°55'05.25"
N 56°04'39.16"

amjath1
Starting Member

6 Posts

Posted - 2007-08-29 : 15:18:21

No the table, column, where condition all are dynamic. I just wrote that simplified form of query above. So, I have to use EXEC.

amjath1
Starting Member

6 Posts

Posted - 2007-08-29 : 15:24:18

Lets say I have a function which has parameters as tablename, column to be selected, and where condition. It has to return the value based on the paramerers. Thats what I'm trying to do. There could be other better ways than these.. Please let me know.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-29 : 15:32:01

why do topics marked urgent always have totaly bad practice all over them???

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-29 : 15:33:31

DECLARE @value1 VARCHAR(1000)
declare @sql nvarchar(4000)
select @sql = 'SELECT @value1 = CAST(mno AS VARCHAR(1000)) FROM servtest WHERE rowno = 1'
sp_executeSql @SQL, N'@value1 VARCHAR(1000) output', @value1 output
select @value1

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 15:37:38

Spirit, don't forget that also the TABLE and COLUMN [to return] are to be dynamic.
Also, the COLUMN [filter] is dynamic, and the filter value.

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-08-29 : 15:42:16

quote:
"why do topics marked urgent always have totaly bad practice all over them???"

Because that's what made them land in the urgent zone!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-29 : 15:44:23

quote:
Originally posted by Peso

Spirit, don't forget that also the TABLE and COLUMN [to return] are to be dynamic.
Also, the COLUMN [filter] is dynamic, and the filter value.

E 12°55'05.25"
N 56°04'39.16"

i'll leave that to him to figure out how to do that.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

amjath1
Starting Member

6 Posts

Posted - 2007-08-29 : 15:46:43

He he.. I agree.. I'm not master in SQL Server. At the same time, i didnt want to dump all my code here..

amjath1
Starting Member

6 Posts

Posted - 2007-08-29 : 15:51:02

DECLARE @value1 VARCHAR(1000)
declare @sql nvarchar(4000)
select @sql = 'SELECT @value1 = CAST(mno AS VARCHAR(1000)) FROM servtest WHERE rowno = 1'
sp_executeSql @sql, N'@value1 VARCHAR(1000) output', @value1 output
PRINT @value1

Looks like something wrong with sp_executeSQL 'Incorrect syntax near 'sp_executeSql'.' Any idea??

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-29 : 15:51:40

exec sp_executeSql ...

i always forget the exec part

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

amjath1
Starting Member

6 Posts

Posted - 2007-08-29 : 15:54:44

WOW.. it works.. You are great.. Spirit..

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-29 : 16:33:29

quote:
Originally posted by spirit1

exec sp_executeSql ...

i always forget the exec part

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Funny, I did the EXACT same thing just the other day ....

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources