Not able to display Query result on the screen
-
This is my first Sp. Iam very bad at writing SP. Need your help.
Req: From the Application Tab we will send tablename and the columnID to the SP and it will display the content.
Sp:
Use ABPharm_DB
CREATE PROCEDURE Display_content_info
@Tablename char(60),
@contentID int
AS
Begin
Declare @tname char(60)
SET @tname = @Tablename
SELECT contentID, contenttext,contentname FROM tname
WHERE ContentId = @contentID
End
GO
EXECUTE Display_content_info @Tablename = 'tblPharmPrepMaster',@ContentID = 1
When I execute this it says command executed successfuly. But it does not display the result in select statement.
Please explain me where Ia m missing any imp info.
Thanks in Advance.
Minaz
"More Green More Oxygen !! Plant a tree today"
-
A few things. First, you need dynamic SQL to do what you are trying to do, and I'm not sure that's something I'd recommend for your first ever SP.
Second, with that said, here's what is needed to accomplish it (untested and no coffee yet):
Use ABPharm_DB
CREATE PROCEDURE Display_content_info
@Tablename sysname,
@contentID int
AS
Begin
DECLARE @strSQL varchar(8000)
-- I'm guessing the example you are working from is doing the following to avoid parameter sniffing issues, so I'm leaving it.
DECLARE @tname sysname
SET @tname = @Tablename
SET @strSQL = '
SELECT contentID, contenttext,contentname FROM ' + @tname + '
WHERE ContentId = ' + @contentID
-- PRINT @strSQL
EXEC (@strSQL)
End
GO
EXECUTE Display_content_info @Tablename = 'tblPharmPrepMaster',@ContentID = 1
-
Thanks for the reply I will try this out.
Minaz Amin
"More Green More Oxygen !! Plant a tree today"
-
I should probably not just give the answer without the obligatory Dynamic SQL disclaimer: The Curse and Blessings of Dynamic SQL.
-
Also - given what you need to extract - you should consider if you couldn't make all of the "content" live in a single SQL table, with an identifier tacked on to tell you what "application table" you're pulling.
so - instead of having 50 separate tables, each with contentID, contenttext,contentname, you have one table (let's call it tblContent), with four fields: contentID, contenttext,contentname and apptablename. in which case your SQL no longer needs to be dynamic:
Create proc ....
...
Select contentID, contenttext,contentname from tblcontent where apptablename=@tablename
....
Of course - you might no longer have the option to change the data model, but if you can - I'd highly recommend it - like david is trying to impress upon you, dynamic SQL is messy, costly, and is often best when avoided.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Since I didn't even notice that the columns were static (I have a tendency to miss stuff when awake, and more so when half-asleep), I'll add to what Matt Miller said. Even if you have no control over the data model, you could wrap a view around the various tables, with an additional column in the view to identify each source table, and then do the SELECT with a WHERE clause as he noted.
-
Thanks Matt and David for your kind reply.
I would add Matt's Idea as fantastic which will really save my time.
Reagrds,
Minaz
"More Green More Oxygen !! Plant a tree today"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply