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 emp2. Select * from DEPSo 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-07-29 : 04:08:46
|
Do you want to return multiple resultsets from the stored procedure?MadhivananFailing to plan is Planning to fail |
|
|
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. |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
manand79
Starting Member
8 Posts |
Posted - 2013-07-30 : 01:31:43
|
Exactly, Please help me on this. |
|
|
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 sqlsomething likeCREATE PROC procnameAS...othercodeDECLARE @SQLQuery varchar(8000)SELECT @SQLQuery = SQLColumnFROM TableEXEC (@SQLQuery)...GO ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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. |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
manand79
Starting Member
8 Posts |
Posted - 2013-07-30 : 02:53:37
|
EmpId intEmp Name varchar(30)Designation Varchar(30) DateOfBirth DatetimeAddress Varchar(500) |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-30 : 05:08:13
|
[code]--table storing queriescreate table querytable(id int identity(1,1),query varchar(2000))--main table having the datacreate 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 = queryFROM querytableEXEC(@Query)output---------------------id val1---------------------1 test12 test23 test54 test65 test7[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 17Incorrect 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. |
|
|
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 17Incorrect 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 queriescreate table querytable(id int identity(1,1),query varchar(2000))--main table having the datacreate table maintable(id int identity(1,1),val1 varchar(100),)insert maintable (val1)select 'test1' union allselect 'test2' union allselect 'test5' union allselect 'test6' union allselect 'test7'insert querytable (query)values ('select * from maintable')DECLARE @Query varchar(2000)SELECT @Query = queryFROM querytableEXEC(@Query) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 StructureId Query1 Select * from emp2 select * from dept3 select * from job Look forward to your quick response. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-06 : 06:23:10
|
[code]CREATE PROC ExecQuery@Id intASDECLARE @Query varchar(2000)SELECT @Query = QueryFROM TableWHERE ID = @IdEXEC(@Query)GOcall it likeEXEC ExecQuery @Id=3[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-08-18 : 10:02:27
|
You need to read this www.sommarskog.se/dynamic_sql.htmlMadhivananFailing to plan is Planning to fail |
|
|
|