How To Read Column Value - 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

SQL Server 2000 Forums

Transact-SQL (2000)

How To Read Column Value

Author

Topic

dehseth
Starting Member

18 Posts

Posted - 2006-12-25 : 07:16:18

Hello ppl,
I am writing a stored procedure and that procedure includes a select sentence which selects a single row and a single column. I want to SET the value of the column into a declared variable. I am new in SQL Server so i dunno how to do it..

can you help me plz?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-12-25 : 07:25:01

Declare @var int
Select @var = Col1 From TableName

Chirag

http://chirikworld.blogspot.com/

dehseth
Starting Member

18 Posts

Posted - 2006-12-25 : 07:34:25

This is not I want exactly. let me make myself clear...

this is my stored procedure:
create procedure sp_getnext @cmd nvarchar(MAX), @rv nvarchar(MAX) OUTPUT as
begin
execute (@cmd)
end

this execute(@cmd) line selects a field from table. @cmd is smt like this: 'Select field1 from tbl1 where id = 1'

after executing this select it returns me a resultset. and I need to get the value inside the resultset and put it into my @rv output variable...

The select sentence is prepared in a function so its dynamically created.

any help ?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-12-25 : 07:49:49

Not Sure about is there any smart way for doing this in SQL Server 2005, but in 2000 this is the way.


create procedure sp_getnext @cmd nvarchar(MAX), @rv nvarchar(MAX) OUTPUT as
begin

	Create Table ##Temp 
	(
		col1 nvarchar(max)
	)
	Insert ##Temp
	execute (@cmd)

	Select @Rv = col1 From ##Temp 

	Drop Table ##Temp 
end

Chirag

http://chirikworld.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-25 : 09:03:05

1 Dont prefix procedure with sp_. By default, Existance of the procedure will be checked in Master database
2 You should use stored procedures with input parameters and pass values to them when executing. Dont send sql statement as paramater as that will lead to sql injunction
3 More on dyanmic sql www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-25 : 09:07:40

quote:
Originally posted by chiragkhabaria

Not Sure about is there any smart way for doing this in SQL Server 2005, but in 2000 this is the way.

create procedure sp_getnext @cmd nvarchar(MAX), @rv nvarchar(MAX) OUTPUT as
begin

Create Table ##Temp
(
col1 nvarchar(max)
)
Insert ##Temp
execute (@cmd)

Select @Rv = col1 From ##Temp

Drop Table ##Temp
end

Chirag

http://chirikworld.blogspot.com/

Note that SQL Server 2000 doesnt support MAX. Only way is declaring Varchar(8000)

Madhivanan

Failing to plan is Planning to fail

dehseth
Starting Member

18 Posts

Posted - 2006-12-25 : 09:46:09

I use sp_executesql word to solve this. thanks to everyone. I am using sql server 2005.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-12-25 : 10:21:50

Note that SQL Server 2000 doesnt support MAX. Only way is declaring Varchar(8000)

I was talking about logic.. i know that

Chirag

http://chirikworld.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-25 : 22:13:22

quote:
Originally posted by dehseth

I use sp_executesql word to solve this. thanks to everyone. I am using sql server 2005.

You should also consider my first reply

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources