January 27, 2009 at 12:13 pm
I currently have 2 tables that i am working with - Locations and FuelTanks. Each location can have many FuelTanks
In my application, users want to view Locations based upon various criteria. For example -
--Show me all of the locations in a particular region (i have a region table)
--Show me all of the locations located near a location (i have a proximity table)
The way the front end of this application works, I have a business objects to represent both location and fueltanks. Fueltanks is a property of location.
I currently have a stored procedure called GetLocationsInRegion. This stored procedure does the following
(simplified)
SELECT * FROM LOCATIONS WHERE REGIONID = @REGIONID
SELECT * FROM FUELTANKS WHERE FUELTANK.LOCATIONID IN( SELECT id FROM LOCATIONS WHERE REGIONID = @REGIONID)...
This stored procedure works perfectly fine. I could set up the Proximity stored proc the same way, but if I needed to change something in the fueltank portion it would have to be maintained in multiple locations
Therefore -
I was thinking I could write a GetFuelTankInformation stored proc that takes as input a list of location ids (whether it is a 1 column table or list of ids - eg. 1, 2, 3, 4, 5)
My problem is:
- you cannot pass a variable of type table as an input parameter to a sql 2k5 stored procedure
- I tried setting it up so that it passed a list of ids but there is a datatype issue. locationid is an int and when I have 1, 2, 3, 4, 5 it is a varchar ..
- I tried passing the sql string to the tank information procedure but i couldnt get the code to work:
in the tank info proc --
CREATE PROCEDURE GetTankInformation
-- Add the parameters for the stored procedure here
-- assume @selectedShopsSQL is a valid sql statement
@selectedShopsSQL varchar
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @SelectedShopsTable TABLE(GasSiteId INT)
-- this is the code that does not work
INSERT@SelectedShopsTable
@selectedShopsSQL
End
Go
Any reccommendations out there to accomplish what I am looking to?
I hope this makes sense
Thanks
sb
January 27, 2009 at 12:26 pm
Not sure, but I think this article may help, give it a read and see if it helps: http://www.sqlservercentral.com/articles/T-SQL/63003/
January 28, 2009 at 10:42 pm
stephenmbell (1/27/2009)
My problem is:- you cannot pass a variable of type table as an input parameter to a sql 2k5 stored procedure
- I tried setting it up so that it passed a list of ids but there is a datatype issue. locationid is an int and when I have 1, 2, 3, 4, 5 it is a varchar ..
- I tried passing the sql string to the tank information procedure but i couldnt get the code to work:in the tank info proc
Any reccommendations out there to accomplish what I am looking to?
You can't pass a table type. But you can. You can't pass a list type. But you can. Note that sql does not support a table nor a list type as you understand those terms in the context of a strongly typed language. If you're a developer with an open and inquisitive mind you will find exactly what you want to do here:
'Passing a table as a parameter'
http://beyondsql.blogspot.com/2007/06/dataphor-13-passing-table-as-parameter.html
'Creating a Super Function'
http://beyondsql.blogspot.com/2007/09/dataphor-all-tables-are-typed-variables.html
And this is without giving up sql server:)
January 28, 2009 at 11:05 pm
Exactly,
I actually accomplished what I was trying to do using a variable of type XML.
I basically pass the XML to a inline table function, and include the xml in my query, and I am able to get the information I am looking for.
Thank you for your posts
sb
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply