"constant" value, dynamic sql

Question

Post reply

"constant" value, dynamic sql

Stef Teal

SSCrazy

Points: 2382
More actions
April 4, 2007 at 8:04 am

#171961

I have the following code in a sp-
            SELECT …………
FROM …
JOIN …
JOIN …
WHERE OB.ObservationID IN
(SELECT TOP 10 OB1.ObservationID
FROM …
WHERE …AND …
ORDER BY OB1.ObservationDate DESC)

I have been asked to remove the hardcoded ‘10’, which makes sense. It was suggested to write a fn which returns the value, so I did and the fn just does this…
CREATE FUNCTION [dbo].[svfGetHistoryNumber]
()
RETURNS int AS
BEGIN
            DECLARE @HistoryCount int
            SET @HistoryCount = 10
            RETURN @HistoryCount
END

So now I’m planning to re-write the sp to incorporate the 10 as a variable, which I think means building the query string into a
variable and executing it dynamically. Something like-

DECLARE @HistoryCnt varchar(10)
SET @HistoryCnt = CONVERT(varchar(10), (SELECT dbo.[svfGetHistoryNumber]()))

DECLARE @strSql varchar(2000)
SET @strSql = ‘SELECT…..’ + @HistoryCnt + ‘OB1.ObservationID….’
EXEC @strSql

This is no big deal, but somewhat of a hassle because my code has some embedded apostrophes and it just seems there should be a more elegant solution.

Suggestions?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Anders Pedersen SSChampion Points: 11410 More actions · Answer 1

in SQL 2005 you can use a variable for the top. Something like this:

declare

@r int

select

@r = 10

select

top (@r) * from sysobjects

So if you need to have it in a function, set the variable = to the function return value first.

Not sure what the point is of what you are doing though since you are still getting a hard coded 10, just now retrieved trough the function.

Stef Teal SSCrazy Points: 2382 More actions · Answer 2

Is the ability to use a variable within a SELECT new to 2005? Is it limited to specific functions, such as TOP?

You are correct, it is still hardcoded but the point is that we may want to use the number 10 in other calls too, so it should only be stored in one designated place. Ideally, we will establish a table for application preferences but we're under pressure now to get the beta out and it's just not going to make this first release.

Thanks for the tip, it worked perfectly.

Anders Pedersen SSChampion Points: 11410 More actions · Answer 3

Not sure what you meant with the follow up question. Using a variable in top is new in 2005 yes.

You could write a select statement like this.

Select @var, column1, column2 from Table

where Column4 = @var2

Stef Teal SSCrazy Points: 2382 More actions · Answer 4

I just thought you had to execute dynamically (with EXEC or sp_executesql) anytime you used a variable in your statement. I was really disappointed w/ SQL Server regarding this when I moved over from FoxPro a few years back.

Do you know of any references that discuss this capability inSQL2005?

Anders Pedersen SSChampion Points: 11410 More actions · Answer 5

There are a multitude of books on the subject of writing queries. But for a start use the Books Online that comes with SQL, for all it;s faults it is good at getting you going to at least know what to look for.