July 22, 2003 at 2:24 pm
Hey all, I'm having trouble with a sproc, I'm mainly a web developer, so I'm coming to you SQL gurus for some help! 🙂 Anyway heres the basics of the sproc I will be passing in a customer_ID, and a description of the info i want, based on the description the view will select * from some table where customer_ID = @customer_ID. Here's what ive come up with so far:
CREATE Procedure p_get_customerdetails
/* Param List */
@Customer_ID int,
@view varchar(20)
AS
Set NOCOUNT ON
Declare @whichview varchar(20)
Select @whichview =
Case
WHEN @view = 'custMain' then 'vw_CUSTOMER'
WHEN @view = 'Devices' then 'vw_CustomerDevices'
When @view = 'Setup' then 'vw_CUSTOMER_Setup'
WHEN @view = 'trxHist' then 'vw_Customer_MaintenaceHistory'
END
Select * From @whichview Where Customer_ID = @Customer_ID
Thanks Ahead of time for any help!
July 22, 2003 at 3:46 pm
I think you're going over the top by choosing a stored procedure for this task. To acheive what you're trying to do within a procedure you'll need to use dynamic SQL, and then you'll lose most of the benefits of using a stored procedure anyway.
I suggest separate stored procedures for each view you're accessing, or just direct selection from the views rather than invoking a procedure.
Cheers,
- Mark
July 22, 2003 at 3:55 pm
Yeah I've been moving that way myself, was hoping to save some time with this but I think i'll just use some inline statements in my dal or sep sprocs for each select statement. Probably the latter.
July 23, 2003 at 2:12 am
Try this instead
CREATE Procedure p_get_customerdetails
/* Param List */
@Customer_ID int,
@view Int =0 AS
Set NOCOUNT ON
/*
0 = CustMain
1 = Devices
2 = Setup
3 = trxHist
*/
IF @view = 0
BEGIN
SELECT * FROM vw_CUSTOMER WHERE CustomerId = @Customer_ID
RETURN 0
END
IF @view = 1
BEGIN
SELECT * FROM vw_CustomerDevices WHERE CustomerId = @Customer_ID
RETURN 0
END
IF @view = 2
BEGIN
SELECT * FROM vw_Customer_Setup WHERE CustomerId = @Customer_ID
RETURN 0
END
IF @view = 3
BEGIN
SELECT * FROM vw_Customer_MaintenaceHistory WHERE CustomerId = @Customer_ID
RETURN 0
END
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply