reuse a stored procedure

[whatsthehampton]

Hi,

I'd like to reuse some stored procedures and just pass a variable to them as the table name from code.

I've read up but cannot find the answer unfortunately.

Something like:

create PROCEDURE [dbo].[KB_GetIDByNodeText]

(
@NodeText nvarchar(50),
@xTable varchar (50)
)

AS

select id from
@xTable
where
NodeText=@NodeText

RETURN

I'm getting "Must declare the table variable "@xTable""


Thanks, j

http://www.iSkool.net

 

[SQLDenis]

I answered this yesterday thread183-1344481

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[philhege]

You can't use a literal variable in place of a table name, unless it is in fact a table variable. You'll have to use dynamic SQL to make this work:


create PROCEDURE [dbo].[KB_GetIDByNodeText]

(
@NodeText nvarchar(50),
@xTable varchar (50)
)

AS
DECLARE @l_vSQL varchar(1024)
SET @l_vSQL =
'SELECT id FROM '
+ @xTable +
' WHERE NodeText='''+@NodeText+''''
EXEC(@l_vSQL)

RETURN


Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Not NULL-terminated yet.

 

[SQLSister]

And dynamic SQl is a Bad thing. Read up on the problems here:

http://www.sommarskog.se/dynamic_sql.html

Questions about posting. See faq183-874

 

[philhege]

OK, if it's so bad, perform the OP's requested operation without it. Show me the code.

I'm tired of people bad-mouthing dynamic SQL when they don't provide a VIABLE alternative.

I'll post my favorite example (the "Convert this Bad Dynamic SQL Challenge") a bit later this week.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Not NULL-terminated yet.

 

[philhege]

*Reads his last post*
Man, I need to get away from this EDI remediation; it's giving me a headache and a grumpy disposition.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Not NULL-terminated yet.

 

[SQLSister]

The viable alternative is to not try to use one proc for everything but to write procs to perform each task that is needed. Reuse of code is a bad thing most of the time in SQL because it is inefficent of the server time. You never ask for more than you actually need at the time of the request. Very few requests need exactly the same fields. And if you vary the fields in each dynamic Sql query it becomes even more of a nightmare to debug or maintain. And if you've ever tried to debug this stuff, you know that you can go mad finding a problem. And that isn't even addresssing the very real security issue it brings up. Bad programming practices are bad practices whether the person wants them to be or not. Trying to "save time" in development at the expense of system performance and security is a bad practice. It will always be a bad practice. By telling people about the problems, we help give them a way to fight the bad practice through their own management chain. Why should those of us who know better than to use these things enable the people who are causing problems down the line in their development.

Questions about posting. See faq183-874

 

[philhege]

Absolutely true. SPs should be as single-purpose as possible.

Here's my dynamic SQL problem:

A report needs to show income for business units. The business units are contained in a table, one per record. Customers are in another table and linked to the business units (many customers per business unit). Orders are in a third table, linked by customer ID (many orders per customer).

Return a table of daily, weekly, monthly and quarterly totals for each business unit without using cursors or dynamic SQL.

TYVMIA.



Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Not NULL-terminated yet.

 

[SQLDenis]

please post DDl and sample data

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[SQLDenis]

here is an example
just define your date params first

[COLOR=blue]select[/color]  ([COLOR=blue]select[/color] sum(amount)  [COLOR=blue]from[/color]
bla [COLOR=green]--rest of joins here
[/color] [COLOR=blue]where[/color] [COLOR=blue]date[/color] >= @Todate and 
[COLOR=blue]date[/color] < @Todate + 1) [COLOR=blue]as[/color] DailyAmount,([COLOR=blue]select[/color] sum(amount)  [COLOR=blue]from[/color]
bla [COLOR=green]--rest of joins here
[/color] [COLOR=blue]where[/color] [COLOR=blue]date[/color] >= @Weekstart and 
[COLOR=blue]date[/color] < @weekend) [COLOR=blue]as[/color] WeeklyAmount,
([COLOR=blue]select[/color] sum(amount)  [COLOR=blue]from[/color]
bla [COLOR=green]--rest of joins here
[/color] [COLOR=blue]where[/color] [COLOR=blue]date[/color] >= @Monthtart 
and [COLOR=blue]date[/color] < @MonthEnd)[COLOR=blue]as[/color] MonthAmount,([COLOR=blue]select[/color] sum(amount)  [COLOR=blue]from[/color]
bla [COLOR=green]--rest of joins here
[/color] [COLOR=blue]where[/color] [COLOR=blue]date[/color] >= @QuarterStart 
and [COLOR=blue]date[/color] < @QuarterEnd) [COLOR=blue]as[/color] QuarterAmount

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[philhege]

Will get to the DDI at lunch. Clarification: the result is a pivot table with the columns named after the business units.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Not NULL-terminated yet.

 

[SQLDenis]

>>the result is a pivot table with the columns named after the business units.

That is display, what ever app gets this data can flip(transpose) it. I have the same reports at my job, I just give it to them like this

Operations 1000000
It 100000
Marketing 300

then the app that displays the data does this

operations it marketing
100000 100000 300

I have close to 200 million rows in one table I can not waste precious processing power flipping and formatting data, Formatting data is done on the client side and NEVER in the DB. Just ask Celko

Just tell them if I do that on the DB site everything else will slow down and yes for pivoting data (if the business units change everytime) you need dynamic SQL no way around it. If they don't change everytime you can hardcode that



Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[SQLDenis]

>>Seems like someone is mistaking that article as being only about the "Curse of Dynamic SQL". It's also about the blessings of it.


I do use Dynamic SQL for maintenance jobs and internal procs that run once in a blue moon, however these procs are not exposed to the outside world and a SQL injection is not possible since only a select group of users have EXEC persmissions on these.

What a lot of people try to do nowadays is have 1 proc where they can pass in the column names as well as table names and they are done. The problem is that performance is not optimal and there is secury risks as well

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[whatsthehampton]

Hi guys,

Thanks for all of your answers which are way above me..

Whilst tempted to go the easy route which does of course work (thanks); I have decided for now to write seperate spocs for each table. I'll be reading up more on this as my interest is now somewhat peaked !.

Thanks all
J

http://www.iSkool.net

 

[philhege]

And Denis' point about "genericizing" SPs is well taken. There's no chance for the engine to create a store a plan if it can't anticipate what's being asked of it. Dynamic SQL does have its place for certain complicated processes, but should be avoided if performance is of any consideration.

Good luck with your coding!

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Not NULL-terminated yet.