dynamic sql query...

Expand Post »

Hi
I am trying to write a function in which I customize my select statement. But it is not working as i desire...

It is as

MS SQL Syntax (Toggle Plain Text)
declare @column_name nvarchar(10)
SET @column_name = 'original'
SELECT @column_name FROM DATA
declare @column_name nvarchar(10)
set @column_name = 'original'
select @column_name from data

Here original is the name of one of the columns. I wanted to make it such that column_name can be assigned any of the column names and the same piece of code works only by changing the value of @column_name...but it is not working....

any ideas ??

Similar Threads

search data base in ASP.NET
Help in using SQL Query in Crstal report with VB.NET in VB.NET
Javascript Function return value into PHP or not ?? Please help !! in JavaScript / DHTML / AJAX
generating dynamic/runtime query in JSP
generating dynamic/runtime query in JSP

sangfroid

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Oct 2006

Permalink

Jun 9th, 2008

Re: dynamic sql query...

MS SQL Syntax (Toggle Plain Text)
CREATE TABLE #tmp (Code varchar(255), Price money, Date smalldatetime)
 
INSERT #tmp (Code, Price, Date)
SELECT 'ProductA', 12.00, '12/10/2002' union ALL
SELECT 'ProductA', 12.50, '01/15/2005' union ALL
SELECT 'ProductA', 12.01, '01/02/2008' union ALL
SELECT 'ProductB', 15.12, '12/01/2005' union ALL
SELECT 'ProductB', 16.00, '01/09/2008' union ALL
SELECT 'ProductB', 16.24, '01/15/2008'
 
declare @column_name nvarchar(10)
SET @column_name = 'Code'
exec ('select ' + @column_name + ' from #tmp')
 
SET @column_name = 'Price'
exec ('select ' + @column_name + ' from #tmp')
 
DROP TABLE #tmp
create table #tmp (Code varchar(255), Price money, Date smalldatetime)

insert #tmp (Code, Price, Date)
select 'ProductA', 12.00, '12/10/2002' union all
select 'ProductA', 12.50, '01/15/2005' union all
select 'ProductA', 12.01, '01/02/2008' union all
select 'ProductB', 15.12, '12/01/2005' union all
select 'ProductB', 16.00, '01/09/2008' union all
select 'ProductB', 16.24, '01/15/2008'

declare @column_name nvarchar(10)
set @column_name = 'Code'
exec ('select ' + @column_name + ' from #tmp')

set @column_name = 'Price'
exec ('select ' + @column_name + ' from #tmp')

drop table #tmp

huangzhi

Reputation Points: 10

Solved Threads: 13

Light Poster

Offline

48 posts
since Feb 2008

Permalink

Jan 13th, 2011

Dynamic Sp

MS SQL Syntax (Toggle Plain Text)
declare @query nvarchar(Max) ,@tbl nvarchar(Max) ,@col nvarchar(Max) ,@val nvarchar(Max) 
  -- give tbl=tablename,col=column,val=value
 SET @query='select @retVal= count(*) from '+@tbl+' where '+@col+' like ''%'+@val+'%'''
 
EXECUTE sp_executesql @query, N'@retVal int OUTPUT', @retVal OUTPUT
 
 SELECT @retVal
declare @query nvarchar(Max) ,@tbl nvarchar(Max) ,@col nvarchar(Max) ,@val nvarchar(Max) 
  -- give tbl=tablename,col=column,val=value
 set @query='select @retVal= count(*) from '+@tbl+' where '+@col+' like ''%'+@val+'%'''
 
EXECUTE sp_executesql @query, N'@retVal int OUTPUT', @retVal OUTPUT
 
 select @retVal

harie.in

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Jan 2011

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Save table data to another table

Next Thread in MS SQL Forum Timeline: Constraint where both columns cannot be null, but one can

DaniWeb Message

Cancel Changes

User Name
Password