Executing sql query from select t_sql from table1

• xmozart.ryan

  SSC Enthusiast

  Points: 176

  More actions

  December 18, 2012 at 7:03 pm

  #271716

  hello guys, can we get recordset from the query that we saved in the recordset ??

  CREATE TABLE [dbo].[t_test] (

  [t_sql] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

  [name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

  ) ON [PRIMARY]

  GO

  insert into t_tes (t_sql,name) values ('select * from view1','select recordset from view1)

  and then i want to call recordset from the query that i saved in t_test with this code :

  DECLARE @query varchar(255)

  SET @query = (select t_sql from t_test)

  select * from (@query)

  but i got err. msg "Must declare the variable '@query'"

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  December 18, 2012 at 11:22 pm

  #1569899

  xmozart.ryan (12/18/2012)

  ---

  DECLARE @query varchar(255)

  SET @query = (select t_sql from t_test)

  select * from (@query)

  but i got err. msg "Must declare the variable '@query'"

  What yiu actually need here the query as result or data of query ?

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• xmozart.ryan

  SSC Enthusiast

  Points: 176

  More actions

  December 19, 2012 at 12:12 am

  #1569912

  Bhuvnesh (12/18/2012)

  ---

  xmozart.ryan (12/18/2012)

  ---

  DECLARE @query varchar(255)

  SET @query = (select t_sql from t_test)

  select * from (@query)

  but i got err. msg "Must declare the variable '@query'"

  What yiu actually need here the query as result or data of query ?

  i need the data of query..

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  December 19, 2012 at 12:22 am

  #1569914

  xmozart.ryan (12/19/2012)

  ---

  Bhuvnesh (12/18/2012)

  ---

  xmozart.ryan (12/18/2012)

  ---

  DECLARE @query varchar(255)

  SET @query = (select t_sql from t_test)

  select * from (@query)

  but i got err. msg "Must declare the variable '@query'"

  What yiu actually need here the query as result or data of query ?

  i need the data of query..

  this will work

  DECLARE @query varchar(255)

  SET @query = ''

  SET @query = 'select t_sql from t_test'

  EXEC (@query)

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• xmozart.ryan

  SSC Enthusiast

  Points: 176

  More actions

  December 19, 2012 at 12:42 am

  #1569917

  Bhuvnesh (12/19/2012)

  ---

  xmozart.ryan (12/19/2012)

  ---

  Bhuvnesh (12/18/2012)

  ---

  xmozart.ryan (12/18/2012)

  ---

  DECLARE @query varchar(255)

  SET @query = (select t_sql from t_test)

  select * from (@query)

  but i got err. msg "Must declare the variable '@query'"

  What yiu actually need here the query as result or data of query ?

  i need the data of query..

  this will work

  DECLARE @query varchar(255)

  SET @query = ''

  SET @query = 'select t_sql from t_test'

  EXEC (@query)

  it's not like that.

  i want to get record set from the query that i already saved on table t_test field t_sql,

  i.e :

  i already saved a sql query like "select * from table" into t_test field t_sql

  after that i want to get that query and save it to variable , and then i want to get record set from that variable..

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  December 19, 2012 at 1:32 am

  #1569924

  If I may suggest...

  If this is just for experimentation, that's fine. If this is a design for an app, just don't do this. You're opening yourself to all sorts of complexities, SQL Injection being just one.

  As for how you do this:

  DECLARE @sSQL NVARCHAR(4000)

  SELECT @sSQL = t_sql from t_test WHERE -- whatever condition to get the row you want.

  EXEC (@sSQL)

  Again, DO NOT go this route/design for a real application.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• xmozart.ryan

  SSC Enthusiast

  Points: 176

  More actions

  December 19, 2012 at 1:45 am

  #1569926

  GilaMonster (12/19/2012)

  ---

  If I may suggest...

  If this is just for experimentation, that's fine. If this is a design for an app, just don't do this. You're opening yourself to all sorts of complexities, SQL Injection being just one.

  As for how you do this:

  DECLARE @sSQL NVARCHAR(4000)

  SELECT @sSQL = t_sql from t_test WHERE -- whatever condition to get the row you want.

  EXEC (@sSQL)

  Again, DO NOT go this route/design for a real application.

  It works,, thanks.. 🙂

  actually from last hour, i want to create a stored procedure which have a dynamic select.. and have a dynamic where too.. and it's really sick for me to create such a dynamic t-sql script...

  so now, i create static stored procedure and it much easier to do.,, thx 4 ur advice man.. 😎