Solved

function to return table name

Posted on 2008-10-01
3
817 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

717 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