July 23, 2003 at 10:33 am
In Access I am opening the below procedure as a recordset and then processing the returned data:
With rst
pubCountrySuperUser = Nz(!countrySuperUser)
pubSecurityUser = Nz(!SecurityUser)
pubEuropeSuperUser = Nz(!EuropeSuperUser)
pubCountryCode = Nz(!CountryCode)
pubVersion = !Version
End With
CREATE PROC [dbo].[procSalesSecurity]
@userid Varchar(7) = NULL
AS
DECLARE @version int
select @version = MAX(VersionNumber) FROM tblVersion
SELECT CountrySuperUser,
SecurityUser,
EuropeSuperUser,
CountryCode, @version as version
FROM Sales
WHERE UserId = @userid
GO
I am returning a single row from Sales but also returning version number from a completely different table.
I would like to return unrelated data from other tables in a similar way using variables and adding these to the final select statement.
Being new to SQL Server, I am wondering whether this is the best way of coding this. I have gone for a single rather than multiple procedures in order to minimize traffic across the WAN.
July 23, 2003 at 11:21 am
I would say that is a good way to accomplish your goal. You do have the ability to do something like this:
select CountrySuperUser, (select MAX(VersionNumber) FROM tblVersion) as Version, col3, col4...
from Sales...
But I like the your way better. If you are returning multiple rows from the Sales table, it is more efficient to include the variable in the select list instead of a subquery.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply