Read Query from file - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Read Query from file

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-03-27 : 16:34:10

Hi There,
From what I see from searches on the internet this looks like an old nugget.
I would like to have a stored procediue that opens a text file with Sql Query code inside and execute that code.

Can that be done?

Thans for your help.

Best Regards,

Steve

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-27 : 18:10:25

My intuition tells me that this is not a good idea for two reasons:

1. Security. I am no expert on security or SQL injection or any related stuff. But this approach seems to be fraught with peril.

2. Error-handling. I don't see good ways of error handling, but may be there are.

If there is another way to meet the business need, that would be my preference.

Having stated all those caveats and disclaimers, I can think of couple of ways to do this. I have never done this, nor have I tested it.

1. Use xp_cmdshell to run sqlcmd. For example:

exec xp_cmdshell 'sqlcmd -U username -P password -S servername -d databasename -i C:\YourFilename.sql';

2. First bulk insert the file into a temporary table, then read from the temporary table and sp_executesql one line at a time (or if there are no batch separators in the file, concatenate the lines with semi-colons and sp_executesql.) This will run the sql commands in the file in the same spid, unlike the first method.

Did I mention that I don't like it? :--)

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-03-28 : 07:08:58

Hi Sunita,

Thanks for your help once again.

The reason I am thinking of using code from a file is because The query below works ok if I know all the types but if someone adds a new type I have to re-write the sql script. I was going to use an external bit of coding that builds the query from running through the relevant tables and writing to an ascii file. This is the query code:
Select
A.Person,
Responsible = SUM(case when B.ResponsibleId = A.Person then 1 else 0 end),
Actionee = SUM(case when B.ActioneeId = A.Person then 1 else 0 end),
HSSE = SUM(case when TypeId = 5 then 1 else 0 end),
Engineering = SUM(Case when TypeId = 1 then 1 else 0 end),
Environmental = SUM(case when TypeId = 2 then 1 else 0 end)
From
(
Select Person = ResponsibleId From tbMatTrack
Union
Select Person = ActioneeId From tbMatTrack
) A
Inner Join tbMatTrack B
On A.Person = B.ResponsibleId
Or A.Person = B.ActioneeId
Group By A.Person

While this query works perfect for me, the parts that read HSSE = SUM(, Engineering = SUM( etc, need to be dynamic but this is static code. I need to take into account all types.

Don't know how I do that.

Best Regards,

Steve

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-28 : 21:13:52

Assuming that when a new code is added, the id and description are inserted into a database table, would one of these be possible?

1. Change your query to join with that table and use dynamic pivoting to get the query results in the form you need. This would still not tell you the column names, but you could just assign col1, col2 etc. to the column names and map those to the correct names in the presentation layer.

2. Change the entire query to be a dynamic query, built based on the number of codes in the code table. You will of course, need to be mindful of risks of SQL injection.

You know your business needs better than I do, so what I am thinking may be completely off the mark, so I will say no more :--)

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-03-31 : 11:40:02

Hi Sunita,
Thanks for the reply.

I managed to get this working with a view, pulling in the type description with a join.
My problem now is I need to transpose everything. I.E. across the top of the returned table is ActioneeId, ResonsibleId, HSSE, Engineering, Standards and the resulting sums below each column.
I need it to be:
ActioneeId,
ResonsibleId,
HSSE,
Engineering,
Standards

Can that be done?

Best Regards,

Steve

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-31 : 11:47:39

Yes you can do it. In your case, since you don't know in advance the various types, you would need dynamic cross-tab. See these links:

http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-03-31 : 12:15:08

quote:
Originally posted by sunitabeck

Yes you can do it. In your case, since you don't know in advance the various types, you would need dynamic cross-tab. See these links:

http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

Hi Sunita,

Yes that crosstab is what I have used, it's fantastic, but I don't know how to transpose the returned table.

Thanks for your help.

Best Regards,

Steve

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-31 : 15:25:03

You would need to unpivot to get it back. But, if you have already pivoted it, and now if you have to unpivot it, it seems like there is something wrong in the logic. But I don't know enough about the problem to say what it might be.

If you do want to unpivot, here is a quick example based on your data, it's standard stuff in SQL 2005 or above.

create table #PivotTable (PersonId int, Engineering int, Environmental Int);
insert into #PivotTable values (1, 10, 15);
insert into #PivotTable values (1, 11, 8);
insert into #PivotTable values (2, 2, 7);
insert into #PivotTable values (2, 9, 31);

select PersonId, TypeName, TypeQty
from #PivotTable
unpivot ( TypeQty for TypeName in ([Engineering],[Environmental]) ) as U

Your columns are dynamic, so you will need a DYNAMIC unpivot. I have not done this or tried it, but I guess you should be able to use dynamic SQL exactly as the "undynamic" that I have above, except you would need to get the columns to be unpivoted from your data tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources