INSERT @Table
SELECT 'A','DECLARE @C INT = 1 SELECT @C+1'
UNION
SELECT 'B','DECLARE @C INT = 5 SELECT @C+6'
SELECT *
FROM @Table
-
1Can you explain what you are trying to achieve?PSK– PSK2019-03-13 05:27:31 +00:00Commented Mar 13, 2019 at 5:27
-
Take look at Exec command or executeSanpas– Sanpas2019-03-13 05:29:34 +00:00Commented Mar 13, 2019 at 5:29
-
you can use execute or sp_executesql learn.microsoft.com/en-us/sql/relational-databases/…Mukesh Arora– Mukesh Arora2019-03-13 05:43:11 +00:00Commented Mar 13, 2019 at 5:43
-
not clear, please show some data and the desired outputRavi– Ravi2019-03-13 07:08:41 +00:00Commented Mar 13, 2019 at 7:08
-
I want to execute query statement that saved in table with other columns of table.samira mahdavi– samira mahdavi2019-03-13 07:26:45 +00:00Commented Mar 13, 2019 at 7:26
Add a comment
|
2 Answers
I think you want to run column B query..
declare @Table as table(a varchar(100),b varchar(500))
INSERT @Table
SELECT 'A','DECLARE @C INT = 1 SELECT @C+1'
UNION
SELECT 'B','DECLARE @C INT = 5 SELECT @C+6'
DECLARE @VAR VARCHAR(500)
SET @VAR=(SELECT B FROM @Table WHERE A='A')
PRINT @VAR
EXEC (@VAR)
4 Comments
samira mahdavi
my table have 66500 rows and I should show all column.
Ravi
@samiramahdavi use loop
Nitika
You can use loop or create single statement from table then execute.
Nitika
You can use loop or create single statement from table then execute.
So you are trying to store the queries in your table and then dynamically call them based on some conditions. then you would want to execute those queries. the below code must work.
create table #table (
rowcnt int identity (1,1),
value1 varchar(10),
query1 varchar(8000))
declare @query varchar(8000);
INSERT @Table
SELECT 'A','DECLARE @C INT = 1 SELECT @C+1'
UNION
SELECT 'B','DECLARE @C INT = 5 SELECT @C+6'
select @query = query1 from #table where id =1
exec (@query)
Please note storing scripts in tables and then calling them is not a good practices. As if the Stored procedure which will call this scripts ever hit any problems it would be a nightmare for debugging it. Also it would be tough for performing performance optimization on DB.
Note if you would need to concat multiple rows for getting the full query use the below code
select @query = COALESCE(@query,'') +query1 from #table where id =1
4 Comments
samira mahdavi
I should use while statement for execute all rows!! this performance is so bad. I have 66000 rows data!
Tejaswi Pandava
With While you will introduce performance issues and i would suggest to avoid it at all cost. Looping is bad for SQl and it may even lead to deadlocks in DB. how frequently are you planning to execute this ?
Tejaswi Pandava
give us some more info to help. 1. How is this table accessed (is it via a batch job, or an event, or anyother way) 2. Frequency of this while loop (do you execute daily, weekly or hourly) 3. Any constraints for it like job must complete its execution in 10mins, 30mins and so on 4. Do your DB has a DB team or do you have access to execution plan.
samira mahdavi
execution more than 20 mins and all user can call this query every day