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 2008 Forums
 Transact-SQL (2008)
 Store query results in through separate Store proc

Author  Topic 

manand79
Starting Member

8 Posts

Posted - 2013-07-29 : 01:20:53
I wanted to write a prodedure which can access any type of queries and provide the results.

For ex. There are 2 query

1. Select * from emp
2. Select * from DEP

So SP can access query and provide the results without knowing the internal functionality of query.

Your prompt help is really appreciable.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-29 : 02:01:31
sorry didnt understand what this question is about. In any case procedure will execute each query included within it and retrieve the result. Didnt understand whats the issue with internal functionality

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-07-29 : 04:08:46
Do you want to return multiple resultsets from the stored procedure?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

manand79
Starting Member

8 Posts

Posted - 2013-07-30 : 00:31:09
For ex. There is one separate query with retrieving employee details. My SP will access that query that provide the result.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-30 : 01:18:33
hmm..do you mean Sp executing an external query stored somewhere may be in a table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

manand79
Starting Member

8 Posts

Posted - 2013-07-30 : 01:31:43
Exactly, Please help me on this.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-30 : 01:35:44
quote:
Originally posted by manand79

Exactly, Please help me on this.



For that you need dynamic sql

something like

CREATE PROC procname
AS
...
othercode

DECLARE @SQLQuery varchar(8000)

SELECT @SQLQuery = SQLColumn
FROM Table

EXEC (@SQLQuery)
...
GO


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

manand79
Starting Member

8 Posts

Posted - 2013-07-30 : 01:49:02
I am fresher and not able to find solution. Can you please send full code with example of emp table. I would really appreciate you for this.

This will help me a lot.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-30 : 01:57:57
can you post structure of table storing the query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

manand79
Starting Member

8 Posts

Posted - 2013-07-30 : 02:53:37
EmpId int
Emp Name varchar(30)
Designation Varchar(30)
DateOfBirth Datetime
Address Varchar(500)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-30 : 03:53:29
hmm...which field stores the query here? I dont think this is table containing the query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

manand79
Starting Member

8 Posts

Posted - 2013-07-30 : 04:08:40
Okay.. Can you please take any dummy table at your convenience. They have only given me this scenario and ask for output.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-30 : 05:08:13
[code]
--table storing queries
create table querytable
(
id int identity(1,1),
query varchar(2000)
)

--main table having the data
create table maintable
(
id int identity(1,1),
val1 varchar(100),
)


insert maintable (val1)
values ('test1'),
('test2'),
('test5'),
('test6'),
('test7')


insert querytable (query)
values ('select * from maintable')

DECLARE @Query varchar(2000)

SELECT @Query = query
FROM querytable

EXEC(@Query)


output
---------------------
id val1
---------------------
1 test1
2 test2
3 test5
4 test6
5 test7

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

manand79
Starting Member

8 Posts

Posted - 2013-07-30 : 13:26:24
Error is coming while I am runnig above code. Error saying "Msg 102, Level 15, State 1, Line 17
Incorrect syntax near ','."
Error is coming on
insert maintable (val1)
values ('test1'),

Please note i am using sql server 2008. Look forward to your positive response.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-31 : 02:00:43
quote:
Originally posted by manand79

Error is coming while I am runnig above code. Error saying "Msg 102, Level 15, State 1, Line 17
Incorrect syntax near ','."
Error is coming on
insert maintable (val1)
values ('test1'),

Please note i am using sql server 2008. Look forward to your positive response.


if using sql 2008 then it should work. if before, use below modification


--table storing queries
create table querytable
(
id int identity(1,1),
query varchar(2000)
)

--main table having the data
create table maintable
(
id int identity(1,1),
val1 varchar(100),
)


insert maintable (val1)
select 'test1' union all
select 'test2' union all
select 'test5' union all
select 'test6' union all
select 'test7'


insert querytable (query)
values ('select * from maintable')

DECLARE @Query varchar(2000)

SELECT @Query = query
FROM querytable

EXEC(@Query)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

manand79
Starting Member

8 Posts

Posted - 2013-08-06 : 06:13:18
Hi, They are not satisfied with answer. Can you please help me.

As per them, below is table structure which store id and query. I have to write store procedure which take id as parameter and display query result from table.

Table Structure

Id Query
1 Select * from emp
2 select * from dept
3 select * from job

Look forward to your quick response.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-06 : 06:23:10
[code]
CREATE PROC ExecQuery
@Id int
AS
DECLARE @Query varchar(2000)

SELECT @Query = Query
FROM Table
WHERE ID = @Id
EXEC(@Query)
GO

call it like


EXEC ExecQuery @Id=3
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-08-18 : 10:02:27
You need to read this www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -