Solved

function to return table name

Posted on 2008-10-01
3
810 Views
Last Modified: 2012-08-14
From a stored procedure, can I call a custom function that will return the table name I want to use in my SQL syntax?

thanks in advance,
Bill
ALTER FUNCTION [GetTableName](@Criteria VARCHAR(1))

RETURNS varchar(50)

AS

BEGIN

DECLARE @Output varchar(50)

SET @Output = ''
 

SELECT @Output = 

CASE WHEN @Criteria = 'A' THEN 'TABLE_A'

WHEN @Criteria = 'B' THEN 'TABLE_B'

WHEN @Criteria = 'C' THEN 'TABLE_C'

END
 

RETURN @Output

END
 
 
 

Create PROCEDURE [dbo].[sp_TestStoreProc]

 SELECT TOP 1 * FROM + GetTableName('B') 

END

Open in new window

0
Comment
Question by:woyler
  • 2
3 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22618645
have to change your proc around bit:

Create PROCEDURE [dbo].[sp_TestStoreProc]
BEGIN
declare @x nvarchar(3000)
set @x = ' SELECT TOP 1 * FROM '+ GetTableName('B')
exec sp_executesql @x
END
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 250 total points
ID: 22618653
also, have to include dbo. on your function call

Create PROCEDURE [dbo].[sp_TestStoreProc]
BEGIN
declare @x nvarchar(3000)
set @x = ' SELECT TOP 1 * FROM '+ dbo.GetTableName('B')
exec sp_executesql @x
END
0
 

Author Closing Comment

by:woyler
ID: 31502150
Perfect.  Thank you for the quick response.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now