Concat columns

I have to do some paging stuff. I am doing the follow in a proc.
Select top 10 * from customer
where @.SearchKey <= LastName+FirstName+CustomerNumber
Question is what index to I build on the database to be able to search
this effectively?
Thank you.
ChrisFirst don't select * - specify the columns you want returned.
Second your condition is probably always going to result in a table scan
because you're not searching columns, you're comparing 2 atomic values for
each record. One value being the variable and the other being the
concatenation of the 3 columns.
Better to:
SELECT col1, col2, colN
FROM dbo.customer
WHERE
LastName >= @.SearchKey
OR FirstName >= @.SearchKey
OR CustomerNumber >= @.SearchKey
You could then build some indexes on any or all of those 3 columns.
"Chris" wrote:

> I have to do some paging stuff. I am doing the follow in a proc.
> Select top 10 * from customer
> where @.SearchKey <= LastName+FirstName+CustomerNumber
> Question is what index to I build on the database to be able to search
> this effectively?
> Thank you.
> Chris
>|||Chris,
SELECT TOP 10 <COLUMN LIST>
FROM CUSTOMER
WHERE CUSTOMERNUMBER = @.CUSTOMERNUMBER --ASSUMING EACH CUSTOMER IS UNIQUE
BY THIS KEY
--CREATE INDEX ON CUSTOMERNUMBER
HTH
Jerry
"Chris" <no@.spam.com> wrote in message
news:eIE$cwP0FHA.3336@.TK2MSFTNGP12.phx.gbl...
>I have to do some paging stuff. I am doing the follow in a proc.
> Select top 10 * from customer
> where @.SearchKey <= LastName+FirstName+CustomerNumber
> Question is what index to I build on the database to be able to search
> this effectively?
> Thank you.
> Chris|||>> Question is what index to I build on the database to be able to search
Try this and see if it makes a difference. Create a computed column like:
concat AS last_name + first_name + cust_nbr.
Then cluster the computed column like:
CREATE CLUSTERED INDEX Idx ON Customers( concat ASC )
Note that this will bias the table access for queries involving this
specific predicate and could potentially slowdown other data manipulation
operations.
Anith|||Jerry Spivey wrote:
> Chris,
> SELECT TOP 10 <COLUMN LIST>
> FROM CUSTOMER
> WHERE CUSTOMERNUMBER = @.CUSTOMERNUMBER --ASSUMING EACH CUSTOMER IS UNIQUE
> BY THIS KEY
> --CREATE INDEX ON CUSTOMERNUMBER
> HTH
> Jerry
> "Chris" <no@.spam.com> wrote in message
> news:eIE$cwP0FHA.3336@.TK2MSFTNGP12.phx.gbl...
>
>
>
That doesn't solve my problem. I'm trying to search for the person
alphabetically after the person I passed in. you way just scans for the
customer id.
Chris|||OK...perhaps you should say that in your original post next time.
"Chris" <no@.spam.com> wrote in message
news:eL$5goQ0FHA.2064@.TK2MSFTNGP09.phx.gbl...
> Jerry Spivey wrote:
> That doesn't solve my problem. I'm trying to search for the person
> alphabetically after the person I passed in. you way just scans for the
> customer id.
> Chris|||Try:
CREATE PROC #APROC
@.LASTNAME VARCHAR(20)
AS
SELECT <COLUMN LIST>
FROM AUTHORS
WHERE LASTNAME > @.LASTNAME
ORDER BY LASTNAME
--CONSIDER CREATING A CLUSTERED INDEX ON LASTNAME
--DROP PROC #APROC
HTH
Jerry
"Chris" <no@.spam.com> wrote in message
news:eL$5goQ0FHA.2064@.TK2MSFTNGP09.phx.gbl...
> Jerry Spivey wrote:
> That doesn't solve my problem. I'm trying to search for the person
> alphabetically after the person I passed in. you way just scans for the
> customer id.
> Chris|||KH wrote:
> First don't select * - specify the columns you want returned.
> Second your condition is probably always going to result in a table scan
> because you're not searching columns, you're comparing 2 atomic values for
> each record. One value being the variable and the other being the
> concatenation of the 3 columns.
> Better to:
> SELECT col1, col2, colN
> FROM dbo.customer
> WHERE
> LastName >= @.SearchKey
> OR FirstName >= @.SearchKey
> OR CustomerNumber >= @.SearchKey
> You could then build some indexes on any or all of those 3 columns.
>
> "Chris" wrote:
>
This won't work.. If I have the name 'Smith, Joe 1234' your solution
will give me as long as their name is getter than Smith or have a first
name greater than joe (i.e. "Apple, Zebra" would be allowed)
Chris|||On Fri, 14 Oct 2005 16:16:20 -0400, Chris wrote:

>I have to do some paging stuff. I am doing the follow in a proc.
>Select top 10 * from customer
>where @.SearchKey <= LastName+FirstName+CustomerNumber
>Question is what index to I build on the database to be able to search
>this effectively?
>Thank you.
>Chris
Hi Chris,
Anith's suggestion to add a computed column and index it is good. But if
that's for some reason unwanted, then try the query below. The extra AND
might look redundant, but your version is unable to use an index on the
LastName column, and the redundant extra AND does enable some
preselection using that index (if there is one, that is).
SELECT TOP 10 Column1, column2, ... -- Don't use SELECT *
FROM customer
WHERE @.SearchKey <= LastName+FirstName+CustomerNumber
AND @.SearchKey <= LastName
By the way, are you aware that using TOP without ORDER BY will yield
undefined results?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||If you have 'Smith, Joe 1234' your solution won't work either, as (I'm
assuming your data doesn't have the spaces and commans in it) concating the
fields will yield 'SmithJoe1234' which would probably fail the condition in
most cases.
Why are you using less than operators for string comparison anyways?
You should be dealing with things like that before it gets to the database.
"Chris" wrote:

> KH wrote:
> This won't work.. If I have the name 'Smith, Joe 1234' your solution
> will give me as long as their name is getter than Smith or have a first
> name greater than joe (i.e. "Apple, Zebra" would be allowed)
> Chris
>

computed columns and casting

Why does the following fail the parser ?
CREATE PROC [dbo].[GetQuestionAnswers]
@.QuestionID int
AS
DECLARE @.TotalCount int
SELECT @.TotalCount = Sum(AnswerCount)
FROM Answer
WHERE QuestionID = @.QuestionID
IF (@.TotalCount = 0)
BEGIN
SET @.TotalCount = 1
END
SELECT AnswerID, AnswerText, CAST(AnswerCount / @.TotalCount AS double) As
AnswerFraction
FROM Answer
WHERE QuestionID = @.QuestionID
ORDER BY Rank
GODouble is not a Transact-SQL system data type. Use float or real for
approximate values or decimal for exact numeric values.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:ecBae3oLFHA.4028@.tk2msftngp13.phx.gbl...
> Why does the following fail the parser ?
> CREATE PROC [dbo].[GetQuestionAnswers]
> @.QuestionID int
> AS
> DECLARE @.TotalCount int
> SELECT @.TotalCount = Sum(AnswerCount)
> FROM Answer
> WHERE QuestionID = @.QuestionID
> IF (@.TotalCount = 0)
> BEGIN
> SET @.TotalCount = 1
> END
> SELECT AnswerID, AnswerText, CAST(AnswerCount / @.TotalCount AS double) As
> AnswerFraction
> FROM Answer
> WHERE QuestionID = @.QuestionID
> ORDER BY Rank
> GO
>|||Hi Dan, and thanks for the response.
I should have phrased my question differently (because I quickly found out
that Double is not SQLDataType).
How to write SQL that creates a float or real or decimal psuedocolumn equal
to the quotient of two integer values (columns of integer type, or
otherwise) -- I do not want truncation to the nearest integer.
Thanks.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23gnckLpLFHA.1308@.tk2msftngp13.phx.gbl...
> Double is not a Transact-SQL system data type. Use float or real for
> approximate values or decimal for exact numeric values.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:ecBae3oLFHA.4028@.tk2msftngp13.phx.gbl...
>|||Use
CAST(AnswerCount / @.TotalCount AS Decimal(12,2))
Madhivanan|||John,
It will not work to cast after dividing. You need to cast or
force implicit conversion before dividing.
Won't work:
cast(AnswerCount / @.TotalCount as double precision)
Either of these will work:
cast(AnswerCount as double precision) / @.TotalCount
(1e0*AnswerCount)/@.TotalCount
Steve Kass
Drew University
John A Grandy wrote:

>Hi Dan, and thanks for the response.
>I should have phrased my question differently (because I quickly found out
>that Double is not SQLDataType).
>How to write SQL that creates a float or real or decimal psuedocolumn equal
>to the quotient of two integer values (columns of integer type, or
>otherwise) -- I do not want truncation to the nearest integer.
>Thanks.
>"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>news:%23gnckLpLFHA.1308@.tk2msftngp13.phx.gbl...
>
>
>|||CREATE PROCEDURE GetQuestionAnswers ( @.my_question_id INTEGER)
AS
SELECT A1.answer_id, A1.answer_txt,
(A1.answer_count
/ (SELECT CASE WHEN SUM(A2.answer_count) = 0
THEN CAST (1.00 AS REAL)
ELSE SUM(A2.answer_count) END)
FROM Answers AS A2
WHERE A2.question_id = A1.question_id))
AS answer_fraction
FROM Answers AS A1
WHERE A1.question_id = @.my_question_id;
Untested; the CASE expression will resolve to the highest data type in
the THEN or ELSE clauses. This saves a local variable and lets the
optimizer do its thing.

Complex use of apostrophes

Hello,

could someone help with this query in a stored proc.?

SET @.SQL='SET '''+ @.avgwgt+''' = '

'(SELECT AVG(AverageWeight)

FROM CageFishHistory where CageID IN ('

+ @.cagearray+')

and ItemDate ='''

+CONVERT(varchar(23),@.startdate)+''')'

EXEC @.SQL

I'm trying to get an average value across dynamically selected rows. (I'm using a list array to deliver the selection to the stored proc). I need to re-use the average value within the procedure,so it's not enough to output it as a column of the resultset - EG. 'Select AVG(AverageWeight) as AvgWgt' . If I take out the @.avgwgt line it works fine, but otherwise I'm getting this error:

"Incorrect syntax near '(SELECT AVG(AverageWeight)

FROM CageFishHistory where CageID IN ('."

It may be that I can access a column of the resultset in the rest of the procedure, and that would help avoid the use of pesky apostrophes, but I don't know how to do it.

please, try like this.

SELECT @.avgwgt=AVG(AverageWeight) FROM CageFishHistory where CageID IN ('+ @.cagearray+') and ItemDate ='''+CONVERT(varchar(23),@.startdate)+''')'

Regards,

Omer Kamal

www.friendspoint.de

|||

I tried this Omer,and got

Conversion failed when converting the varchar value ' + @.cagearray + ' to data type int.

|||

Seems to be working now with the Split() Function I found - I'm posting in case it will help others...

SELECT

@.avgwgt=AVG(AverageWeight)FROM CageFishHistorywhere CageIDIN(SELECT ItemFROM dbo.Split(@.cagearray,','))and ItemDate=''+CONVERT(varchar(23),@.startdate)

====================================================================

set

ANSI_NULLSON

set

QUOTED_IDENTIFIERON

go

CREATE

FUNCTION [dbo].[Split]

(

@.ItemList

NVARCHAR(4000),

@.delimiter

CHAR(1)

)

RETURNS

@.IDTableTABLE(ItemVARCHAR(50))

AS

BEGIN

DECLARE @.tempItemListNVARCHAR(4000)SET @.tempItemList= @.ItemListDECLARE @.iINTDECLARE @.ItemNVARCHAR(4000)SET @.tempItemList=REPLACE(@.tempItemList,' ','')SET @.i=CHARINDEX(@.delimiter, @.tempItemList)WHILE(LEN(@.tempItemList)> 0)BEGINIF @.i= 0SET @.Item= @.tempItemListELSESET @.Item=LEFT(@.tempItemList, @.i- 1)INSERTINTO @.IDTable(Item)VALUES(@.Item)IF @.i= 0SET @.tempItemList=''ELSESET @.tempItemList=RIGHT(@.tempItemList,LEN(@.tempItemList)- @.i)SET @.i=CHARINDEX(@.delimiter, @.tempItemList)ENDRETURN

END

complex SQL (for me) step 2

hello, Lets look this Proc :

set nocount on
create table Histo
(
Id varchar(10)primary key,
Week varchar(10),
Project varchar(10)
)
insert into Histo (Id, Week, Project)
select '47','2006-12','Internet'
union all
select '48','2006-13','Internet'
union all
select '49','2006-12','Intranet'

go

select*
from Histo

go

SELECT Project,
[2006-12],
[2006-13]
FROM
(SELECT Project, week, id
FROM histo) s
PIVOT
(
max(id)
FOR Week IN ([2006-12],[2006-13])
) p
ORDER BY [Project]
go

drop table histo

there are 2 blocks of data "hard coded" ([2006-12], [2006-13]) and I want to have something more elegant.

I want to be able to set a begin week and a number of weeks displayed.

Can anyone help me ? Thanks a lot

No help for you there. These value have to be hard coded in the query to work. You will need to use dynamic SQL to build your statement to make it more elegant (well, when speaking of dynamic SQL, elegant is clearly not the right word)

It shouldn't be too hard to dynamically generate the date blocks '[2006-12],[2006-13]', so it might be reasonable.

Go here to vote/discuss on this feature so Microsoft will hear us :)http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=6f168645-a348-4644-b369-63849d5b8355

|||Thank a lot for your help !

complex query help needed

Hello all,

I'm stuck with this one:

Step 1
I have a stored proc like this

SELECT this, that, another, ToDo0, ..., ToDo8 FROM table1 LEFT OUTER JOIN view1 on table1.id = view1.id
WHERE (bunch of criteria)

view one basically returns ToDo0 ... ToDo8

Everything works fine.

Step 2

As I have different ToDo's depending on who is logged on, there are view2 ... view6 returning the ToDo's accordingly. So I have

If @.grp = 1
SELECT this, that, another, ToDo0, ..., ToDo8 FROM table1 LEFT OUTER JOIN view1 on table1.id = view1.id
WHERE (bunch of criteria)
else if @.grp = 2
SELECT this, that, another, ToDo0, ..., ToDo8 FROM table1 LEFT OUTER JOIN view2 on table1.id = view2.id
WHERE (same bunch of criteria)
else ... (you get the point :)

works fine, though a little slow.

Step 3

To keep things maintainable (I'm not the only one working on that) and somewhat modular, I'd like to have something like

SELECT this, that, another, ToDo0, ..., ToDo8 FROM table1 LEFT OUTER JOIN just-take-the-right-view-please as Yep on table1.id = Yep.id
WHERE (bunch of criteria)

No Go ...

I tried:

1.
Create #ttbl_ToDo (...)
if @.grp = 1
Insert #ttbl_ToDo SELECT * from view1
else ...

and then joining on the #ttbl

I got timeouts (view1 ... 6 are quite expensive).

2.
Built a stored proc that already returns ToDo1 .. 8 for the right group but then I can't access the resultset from the calling sp.

3.
Try to build dynamic SQL with EXEC (expected timeouts there, too) - the SQL string exceeds maximum length (as things are a little more complex in reality)

I'm using MSSQL 7 (no option to migrate to 2000 an use functions yet :( )

Some more explanation why I'm not happy with Step2 (which at least is working):
1. the where clause is kind of complex an needs to be adopted from time to time. It's just a pain to do this 6 times.
2. Other developers should be able to add ToDo-groups without changing the query itself. Changing the part with the temp table wouldn't be perfect but acceptable, but changing the whole thing is not what we want.

Any hints are appreciated.

TIA, ChrisTo simplify you could use dynamic SQL, which as you stated cause timeouts, so I don't know if this will help.

DECLARE @.view varchar(35)

SELECT @.view = CASE
WHEN @.grp=1 THEN "view1"
WHEN @.grp=2 THEN "view2"
WHEN @.grp=3 THEN "view3"
WHEN @.grp=4 THEN "view4"
WHEN @.grp=5 THEN "view5"
ELSE "view6"
END

EXEC ("SELECT ... FROM... " + @.view + " WHERE...")

If your views only differed by a WHERE clause like this "@.grp" value then you could combine the views into one view and leave off the WHERE criteria until you use it.

CREATE VIEW view1 AS
SELECT .....
WHERE grp = 1

CREATE VIEW view2 AS
SELECT .....
WHERE grp = 2

etc.

Change to

CREATE VIEW view AS
SELECT .....

Then
SELECT this, that, another, ToDo0, ..., ToDo8 FROM table1 LEFT OUTER JOIN view on table1.id = view.id
WHERE (bunch of criteria)
AND view.grp = @.grp <-- Add here

This would not require dynamic SQL.

Also you can execute a stored procedure and have it's result go into a table.

INSERT table EXEC myProc

Note:
You have to watch out with views they are not a performance saver. If the view is a 6 table JOIN then when you execute it, it is a 6 table JOIN.

Complex PROC question

Complex to me, anyways. I posted this quite a bit ago, although the question was different, and you guys pointed out what was wrong, which saved me lots of headaches and coffee, so hopefully you guys can point out what is wrong this time.

This PROC is used in a search engine type script I wrote that searches through a database of magazine articles. You can search with just a name or description, or a range of dates. You can also search for all articles posted after a date, or before a date. On the perl side, I pass it 4 parameters, $querry_string, the description or name, $datefrom and $dateto, which specifies a range of dates, and $slice, which is which slice of results to return, like 1-10.

The script *mostly* works, it works correctly with two dates or no dates are specified, however it will not work with only one date filled in. ( See querry2 in the proc ) Any ideas what is wrong? When I run the line in Querry Analyzer it works, but in the script, it finds 0 results no matter what.

CREATE PROC [dbo].[search_querry_results](
@.datefrom datetime = NULL,
@.dateto datetime = NULL,
@.querry_string varchar(60),
@.slice int
)
AS

DECLARE @.querry nvarchar(2000)
DECLARE @.querry2 nvarchar(2000)
DECLARE @.querry3 nvarchar(2000)

-- Care of blind dude

SET @.querry = 'SELECT TOP ' + CAST(@.slice AS varchar(100))
+ ' * FROM FREETEXTTABLE( Exponent, *, ''' + @.querry_string
+ ''' ) as ct JOIN Exponent AS e ON ct.[KEY] = e.[Key] WHERE date > '''
+ CAST( @.datefrom AS varchar(30)) + ''' AND date < '''
+ CAST(@.dateto AS varchar(30)) + ''' ORDER BY Rank DESC'

SET @.querry2 = 'SELECT TOP ' + CAST(@.slice AS varchar(100))
+ ' * FROM FREETEXTTABLE( Exponent, *, ''' + @.querry_string
+ ''' ) as ct JOIN Exponent AS e ON ct.[KEY] = e.[Key] WHERE date < '''
+ CAST(@.dateto AS varchar(30)) + ''' ORDER BY Rank DESC'

SET @.querry3 = 'SELECT TOP ' + CAST(@.slice AS varchar(100))
+ ' * FROM FREETEXTTABLE( Exponent, *, ''' + @.querry_string
+ ''' ) as ct JOIN Exponent AS e ON ct.[KEY] = e.[Key] ORDER BY Rank DESC'

BEGIN
IF ( @.datefrom IS NOT NULL ) AND ( @.dateto IS NOT NULL )
EXEC sp_executesql @.querry

ELSE


IF ( @.dateto IS NOT NULL ) AND ( @.datefrom IS NULL)
EXEC sp_executesql @.querry2


ELSE


IF ( @.datefrom IS NULL ) AND (@.dateto IS NULL )
EXEC sp_executesql @.querry3
END
GO

There is the code. Let me know if you can come up with something, im all out of ideas. Thanks

-ruhkWhat is the error you're getting?

If you CAST a NULL to varchar, you get NULL. So I'm not sure if that's where you're problem is occurring or not...|||Hi!, the "date" field in the table is (VarChar or NVarchar) or DateTime?
if it's Datetime, i think the better way is comparing it as datetime. You don't have to convert anything because @.DateTo and @.DateFrom are already DateTime, so compare it directly.

Sorry for my bad english, i'm a programmer, not a biligue, jejeje. I hope this will be usefull, i spend much time figting with date comparisons and that's the way i found it works.
Suerte!!!!!!|||Try the following.

I changed:
- Compare actual dates rather than string version of dates
- Cleaned up the conditions of datefrom/dateto being NULL. The code below is much easier to understand/maintain.
- Avoided escaping issues by leaving @.querry_string as a variable reference in the dynamic SQL rather than dynamically adding the actual value to the dynamic SQL.

<code>
CREATE PROC [dbo].[search_querry_results](
@.datefrom datetime = NULL,
@.dateto datetime = NULL,
@.querry_string varchar(60),
@.slice int
)
AS

DECLARE @.query varchar(2000)

SET @.query = 'SELECT TOP ' + CAST(@.slice AS varchar(100))
+ ' * FROM FREETEXTTABLE( Exponent, *, @.querry_string ) AS ct'
+ ' JOIN Exponent AS e ON ct.[KEY] = e.[Key]'

IF ( @.datefrom IS NOT NULL ) AND ( @.dateto IS NOT NULL )
SET @.query = @.query + ' WHERE Date BETWEEN @.datefrom AND @.dateto'
ELSE IF ( @.datefrom IS NOT NULL )
SET @.query = @.query + ' WHERE Date > @.datefrom'
ELSE IF ( @.dateto IS NOT NULL )
SET @.query = @.query + ' WHERE Date < @.dateto'

SET @.query = @.query + ' ORDER BY Rank DESC'

EXEC sp_executesql @.query
</code>|||Roger - Tried using your code and it gives me a Procedure expects parameter '@.statement' of type 'ntext/nchar/nvarchar'. (SQL-37000)(DBD: st_execute/SQLExecute err=-1) error. Going to try to figure out what that means.|||Ah, I changed the declare line to DECLARE @.query nvarchar(2000), and that got rid of one problem. However now it says I must declare @.querry_string, and if I add in ''' + @.querry_string + ''' it gives me errors that I must declare my other variables. Anyone know how to fix this?|||Comment out your EXEC statements temporarily. Then add code to display your SQL strings:

select @.querry1
select @.querry2
select @.querry3

Then try running each string through Query Analyzer to help debug your dynamic code.|||They all works perfectly in querry analzyer but when I run my origonal code I posted in the script, it returns 0 hits.

I think its a problem with the datetime. For some reason WHERE Date < @.Dateto does not work.|||I fixed it! Thank you RogerWilco and others, your code worked great. The problem was actually in another stored procedure that counted just the hits.

Complex DB Search Forms (Store Proc vs. Complex Where)

I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.

Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.

I build clauses like this (i.e., 4 fields shown):

SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )

My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.You can achieve the exact same result either way you do it. It is more object oriented to pass the parameters into the SP and then build your WHERE clause. Are you good at TSQL? If so then why not put them in a SP. If you have never written an SP before then stay with what you know and write the code in your page.

I have done it both ways many many many times, it all depends on the situation. You will have to write the same code in the SP than where it is now, just different syntax.

HTH|||Ok, that makes me feel better. I know it's generally prefered to use SP, but I've never written one. If I did make one, I guess it'd be like a function with 15 parameters?

I'm doing enough learning with ASP.NET, so I'll hold off on SP's for now.

I'll have to find me a good SP book. Performance isn't an issue now, but hopefully it will be!|||You are not really giving up that much performance. My applications generally use very large databases (11 million+ records) with very complex and dynamic Where clauses. If all tables are indexed properly then the performance difference is not very evident.

Use your current constraints as your guide (time, etc.).|||If you are curious how this issue was resolved at this ASP.NET Forum (which we are all posting at now), I am posting below the stored procedure used to search the fourms. You can see that a parameter @.SearchTerms is used to hold the whole where clause. I like this way because it is easier to build dynamically the where clause in c# or vb.net and then make use of the better performance that sp provides. Furthermore this sp below gives you the ability to display only a given number of results (very convenient to use with a datagrid with paging)

CREATE PROCEDURE forums_GetSearchResults
(
@.SearchTerms nvarchar(500),
@.Page int,
@.RecsPerPage int,
@.UserName nvarchar(50)
)
AS
CREATE TABLE #tmp
(
ID int IDENTITY,
PostID int
)
DECLARE @.sql nvarchar(1000)
SET NOCOUNT ON
SELECT @.sql = 'INSERT INTO #tmp(PostID) SELECT PostID ' +
'FROM Posts P (nolock) INNER JOIN Forums F (nolock) ON F.ForumID = P.ForumID ' +
@.SearchTerms + ' ORDER BY ThreadDate DESC'
EXEC(@.sql)
-- ok, all of the rows are inserted into the table.
-- now, select the correct subset
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage
SELECT @.LastRec = (@.Page * @.RecsPerPage + 1)
DECLARE @.MoreRecords int
SELECT @.MoreRecords = COUNT(*) FROM #tmp -- WHERE ID >= @.LastRec

-- Select the data out of the temporary table
IF @.UserName IS NOT NULL
SELECT
T.PostID,
P.ParentID,
P.ThreadID,
P.PostLevel,
P.SortOrder,
P.UserName,
P.Subject,
P.PostDate,
P.ThreadDate,
P.Approved,
P.ForumID,
F.Name As ForumName,
MoreRecords = @.MoreRecords,
Replies = (SELECT COUNT(*) FROM Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
P.Body,
P.TotalViews,
P.IsLocked,
HasRead = 0 -- not used
FROM
#tmp T
INNER JOIN Posts P (nolock) ON
P.PostID = T.PostID
INNER JOIN Forums F (nolock) ON
F.ForumID = P.ForumID
WHERE
T.ID > @.FirstRec AND ID < @.LastRec AND
(P.ForumID NOT IN (SELECT ForumID from PrivateForums) OR
P.ForumID IN (SELECT ForumID FROM PrivateForums WHERE RoleName IN (SELECT RoleName from UsersInRoles WHERE username = @.UserName)))
ELSE
SELECT
T.PostID,
P.ParentID,
P.ThreadID,
P.PostLevel,
P.SortOrder,
P.UserName,
P.Subject,
P.PostDate,
P.ThreadDate,
P.Approved,
P.ForumID,
F.Name As ForumName,
MoreRecords = @.MoreRecords,
Replies = (SELECT COUNT(*) FROM Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
P.Body,
P.TotalViews,
P.IsLocked,
HasRead = 0 -- not used
FROM
#tmp T
INNER JOIN Posts P (nolock) ON
P.PostID = T.PostID
INNER JOIN Forums F (nolock) ON
F.ForumID = P.ForumID
WHERE
T.ID > @.FirstRec AND ID < @.LastRec AND
P.ForumID NOT IN (SELECT ForumID from PrivateForums)

SET NOCOUNT OFF|||::You can achieve the exact same result either way you do it.

No, not EXACTLY the same result.

::It is more object oriented to pass the parameters into the SP and then build your WHERE
::clause.

Hmpf. Please dont tell me you believe this.

I mean, frankly, in how far is it more OBJECT ORIENTED to actually write a non-objectoriented METHOD (an SP is nothing more)?

More strucutre, maybe, better practice, maybe, but not more "object oriented".

Some negatives:

* Be sure to have automatic recompile switched on for this SP, as otherwise you just say goodbye to performance for your type of query.

Let see the last post:

::Furthermore this sp below gives you the ability to display only a given number of results
::(very convenient to use with a datagrid with paging)

Yea, righ. It is not exactly like you could not do it right in dynamic SQL either. Frankly, the correct way to ask for only X result is "SELECT TOP X". I somehow miss this on the SP.

* YOu dont need a temp tabl. Use query materialization to use actually use the optimizer. The use of a temp table is about the last way I would have tried to solve this.
* Frankly, the approach of selecting ALL posts into a temp table, just to then only select a subset of this sounds horrible performance wise.
* The "IF" makes not really a lot of sense - the two cases can be put into one pretty easily.|||>>No, not EXACTLY the same result.

Is the end result not a Query ?|||thona,

perhaps you are right about the IF statement. I think this stored procedure together with the ASP.NET Forums are written by a microsoft team (so it says in the agreement that comes with the free download of the forums).

Perhaps there is a better way. could you please give example of "Use query materialization to use actually use the optimizer" because I am not very familiar with this technique?

SELECT TOP ... will only work the first time you select from the database. but how would you handle displaying next result sets?
That is why the proc takes @.page and @.recsperpage parameter in order to determine from which to which result to return. If the user sees 10 results per page and he is requesting the second result set then the proc will calculate @.FirstRec and @.LastRec. But you cannot use these two in a querry against the whole database. you can apply them in a second select only against the relevant results.

If I am wrong, I will be grateful if you help me understand my mistake as efficient searching is very important indeed

Compiling SQL statement in stored proc

(I'm sure this is possible, so maybe someone can just mention the topic that covers it and I'll research it from there.)

In essence, I have two SQL statements that are 98% the same, but I want to substitute a few different clauses according to the variable passed in.

I can get the following simple example to work fine, as long as the CASE statement adds a simple string.
But syntax errors appear when trying to use more complex SQL statements, such as FROM or JOIN clauses, in the CASE statement. I think it doesn't like keywords next to 'WHEN'.

If it's possible to compile the SQL statement as one long string while substituting the conditional clauses, that would work too. That's what I already do in VBScript on the ASP page querying the DB, but I'd like to remove the 1.5-page-long SQL statement from the code to the database, and just pass in one of two options.

The commented-out lines represent my failed attempt. Clues?

CREATE PROCEDURE dbo.tstsp
@.usr char(1)

AS

SET NOCOUNT ON
BEGIN

--DECLARE @.strSQL varchar(3000)
--SET @.strSQL = 'SELECT * FROM dbo.sur_followup WHERE '

SELECT * FROM dbo.sur_followup WHERE USERNAME =

CASE @.usr
-- WHEN 'a' THEN SET @.strSQL = @.strSQL + 'USERNAME = '"ASnow" '
-- WHEN 'b' THEN SET @.strSQL = @.strSQL + ''response_id = 214'
WHEN 'a' THEN 'ASnow'
WHEN 'b' THEN 'JUser'
END

--EXEC @.strSQL

SET NOCOUNT OFF
END
(This example just shows the idea...my query is actually much more complex.)
Thanks!

Hi,

I'm not sure I fully understand your problem, but I have a few initial responses

1) As Umachandar recently explained in the post on "Paging large result sets", you should be using sp_execsql so that you have an opportunity for plan caching.

2) If you're attempting to use "more complex SQL statements, such as FROM or JOIN clauses, in the CASE statement" then they need to evaluate to scalar expressions. You must enclose subqueries in () when the result is needed in a scalar expression context. Here is a silly, simplified example that illustrates the point:

DECLARE @.id int
SELECT @.id = 2
SELECT * FROM sysobjects
WHERE name = CASE @.id
WHEN 1 THEN (SELECT name FROM sysobjects WHERE id=@.id)
WHEN 2 THEN (SELECT name FROM sysobjects WHERE id=@.id)
-- etc
ELSE '?'
END

Does this answer your question?!

Regards,
Clifford Dibble|||

Thanks for the reply, Clifford.

Perhaps a snippet of VBScript code will explain better. This is what I want to do in the stored proc:

[...lots of SQL before this...]
If strRptType = "naddr" Then strSQL = strSQL & "FROM SUR_RESPONSE "

If strRptType = "yaddr" Then
strSQL = strSQL & "FROM SUR_FOLLOWUP "
strSQL = strSQL & "LEFT JOIN SUR_RESPONSE "
End If

strSQL = strSQL & "LEFT JOIN SUR_RESPONSE_ANSWER ON SUR_RESPONSE.RESPONSE_ID = SUR_RESPONSE_ANSWER.RESPONSE_ID "
strSQL = strSQL & "LEFT JOIN SUR_ITEM ON SUR_RESPONSE_ANSWER.ITEM_ID = SUR_ITEM.ITEM_ID "

If strRptType = "yaddr" Then strSQL = strSQL & "ON SUR_FOLLOWUP.response_id = SUR_RESPONSE.RESPONSE_ID "

strSQL = strSQL & "WHERE "

[...lots of SQL after this...]
I think what I want to do is called 'dynamic SQL.' I basically would need to compile the SQL string into a variable, but substitute certain clauses according to the parameter passed into the stored proc. I tried using CASE/WHEN instead of IF/THEN but I guess I don't know the proper syntax for all this.

|||Since you're stuck on doing dynamic SQL, just do the string manipulation in your language of choice (since it will undoubtedly handle strings better/easier) and pass the whole thing on to SQL server as the text of a command object and lose the stored procedure since it's not gaining you anything anyway (be sure to parse the strings of the where clause to avoid SQL injection attacks).

When I'm writing stored procedures with multiple where clauses driven by parameters, I take the laborious route of actually writing the whole stored proc with loads of if statements. E.g.

create procedure db.SomeTableGet(
@.p_lVar1 integer = null,
@.p_lVar2 integer = null,
@.p_lVar3 integer = null)

if @.p_lVar1 is null

if @.p_lVar2 is null

if @.p_lVar3 is null

select
t.Col1,
t.Col2,
t.Col3
from
Table t

else

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col3 = @.p_lVar3

else

if @.p_lVar3 is null

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col2 = @.p_lVar2

else

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col1 = @.p_lVar1
and t.Col2 = @.p_lVar2

else

if @.p_lVar2 is null

if @.p_lVar3 is null

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col1 = @.p_lVar1

else

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col3 = @.p_lVar3
and t.Col1 = @.p_lVar1

else

if @.p_lVar3 is null

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col2 = @.p_lVar2
and t.Col1 = @.p_lVar1

else

select
t.Col1,
t.Col2,
t.Col3
from
Table t
where
t.Col1 = @.p_lVar1
and t.Col2 = @.p_lVar2
and t.Col1 = @.p_lVar1

As you can see, the sp's get long in a hurry, but you can copy and paste most of it, and if you're really enterprising, you can either write a tool to do most of it, or use CodeSmith scripts to do it for you. Also, this kind of thing messes with SQL Server's parameter guessing logic "stuff", but oh well. I wonder if anyone has any ideas on a better way to do it while keeping it in a stored procedure and avoiding exec'ing SQL strings? The main purpose here is to keep the sorting and filtering on the SQL server instead of pushing it off into the business tier that isn't very good at filtering or sorting and doesn't have the advantage of SQL Server's table indexes (and to keep from transferring larger-than-necessary recordsets from the SQL box to the business tier box).|||

Hi Robert
I always write my SPs with multiple where clauses driven by parameters as follows

create procedure dbo.SomeTableGet
@.p_lVar1 integer = 0, -- zeros not nulls cos null <> null
@.p_lVar2 integer = 0,
@.p_lVar3 integer = 0
as
select t.Col1,t.Col2,t.Col3
from Table t
where (case when @.p_lVar1 = 0 then 0 else t.Col1 end )= @.p_lVar1
And (case when @.p_lVar2 = 0 then 0 else t.Col2 end )= @.p_lVar2
And (case when @.p_lVar3 = 0 then 0 else t.Col3 end )= @.p_lVar3

|||

Hi DN,

I think I finally understand your issue. The key point for me was "tried using CASE/WHEN instead of IF/THEN"

Here is the syntax for the CASE expression.

CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

Notice that (a) this is an expression, so it must produce a value and (b) the same holds for result_expression.

But, SET is not an expression. To see this, try this in a query window:

DECLARE @.x char(1)
SET @.x = 'a'

and notice that no value is produced.

Therefore, this is an illegal expression and so you get a syntax error

SELECT
CASE @.x
WHEN 'a' THEN SET @.y = '1'
WHEN 'b' THEN SET @.y = '2'
END
Server: Msg 156, Level 15, State 1, Line 7

Does this answer your question?!

Regards,
Clifford Dibble

compile SP after creation?

Hello
Is it possible to force SQL Server to compile stored procedures after
creation? I mean, when I execute something like:
CREATE PROC SP1 AS
select * from X
where table X does not exist, I want to get error or warning. Now the proc
is created and the error is thrown only when it is executed. We have more
then 1500 stored procedures in the database now scripted out into sql
scripts. We want to ensure that scripts (and all objects like SPs, UDFs,..)
are valid after developers updates but by simply running the script to creat
e
DB and all its content does not produce any warning/error. How can we
accomplish that?
Thanks
eXavierHi
i can think about adding IF OBJECT_ID('Table') IS NULL which means if the
NULL then object does not exist and your error will be thrown
"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs,
> UDFs,..)
> are valid after developers updates but by simply running the script to
> create
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier|||Hi,
I'm not sure if I understand what you mean. Do you mean to write some sql
script parser, extract all table names from all stored procedures and then
call your IF check? This is not acceptable for us. In fact we want the serve
r
to compile procedures to get eventual errors.. SP is compiled when executed
first but we cannot automatically execute them all as they have different
parameters..
Any other idea?
Thanks
eXavier
"Uri Dimant" wrote:

> Hi
> i can think about adding IF OBJECT_ID('Table') IS NULL which means if the
> NULL then object does not exist and your error will be thrown
>
>
> "eXavier" <eXavier@.community.nospam> wrote in message
> news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
>
>|||Specify WITH RECOMPILE in your stored procedure. The procedure will not be
cached and will recompile at runtime.
--
MG
"eXavier" wrote:

> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs, UDFs,..
)
> are valid after developers updates but by simply running the script to cre
ate
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier|||Hi,
WITH RECOMPILE does exactly what you wrote. But it does not help in creation
time - you can still create procedure referencing non-existant tables.
The error is thrown only when the proc is executed, coming back to our
problem. (Further, if the SP compiles we want it to be cached.)
"MGeles" wrote:
[vbcol=seagreen]
> Specify WITH RECOMPILE in your stored procedure. The procedure will not b
e
> cached and will recompile at runtime.
> --
> MG
>
> "eXavier" wrote:
>|||I'm afraid you cannot do that
"eXavier" <eXavier@.community.nospam> wrote in message
news:4BBE6A41-D987-4A5C-A953-1BD71A01A972@.microsoft.com...[vbcol=seagreen]
> Hi,
> I'm not sure if I understand what you mean. Do you mean to write some sql
> script parser, extract all table names from all stored procedures and then
> call your IF check? This is not acceptable for us. In fact we want the
> server
> to compile procedures to get eventual errors.. SP is compiled when
> executed
> first but we cannot automatically execute them all as they have different
> parameters..
> Any other idea?
> Thanks
> eXavier
> "Uri Dimant" wrote:
>|||"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation?
Wayyy back in the day (SQL 6.5 I believe) this was possible, since SQL
checked for the existence of objects before creating stored procedures.
Apparently this caused a bunch of headaches, especially with database object
scripts that weren't created in the proper order, so "deferred name
resolution" was introduced. Table names are not resolved until run-time
instead of at creation time. Your best bet might be to 1) Do what Uri
suggested and explicitly check for the existence of tables yourself before
running the CREATE SP statement, or 2) Do what MS suggests below and test
your code after creation. Uri's suggestion should not be all that
difficult. Maybe a single script that tests for the existence of *all*
tables that are supposed to be in the database. You could run this script
before you try to run any SP creation scripts, and abort the mission based
on the result.
Here's what MS has to say on deferred name resolution:
"Deferred Name Resolution. Deferred name resolution allows procedure
compilation without all table references being present. Deferred name
resolution works in much the same way as the object-oriented concept of late
binding. At compile time, the compiler attempts to resolve all table names
that the procedure references. But if a table does not yet exist, the
compiler defers this name resolution until execution time.
For developers who have used temporary tables within their stored procedures
or triggers, this subtle new feature is long overdue. Although this feature
is useful, it has a side effect that many developers might initially
miss-the compiler no longer reliably catches table-name typos. Yes, you now
must test your code. This statement might sound funny at first, but if you
are not aware of deferred name resolution, you might find yourself wondering
why the compiler missed this error."
(From
http://www.microsoft.com/technet/pr...loy/migrat.mspx)|||Hi eXavier,
xyz's suggestion is reasonable. Appreciate your understanding that this
requirement is individual and actually limited by the design of SQL Server.
It is impossible for us to change the native behavior. I noticed that you
just wanted to ensure that scripts are valid after developers updates but
by simply running the script to create DB and all its content does not
produce any warning/error. You may consider to find a way from management.
For example, setup a test database environment which is same as the
development database then script a file to execute all the SPs or UDFs
that you want to check. This may require a standard process that the
developer of a SP or a UDF should provide a SQL test statement which can be
directly copied into the script file for checking at runtime. If some
errors are thrown out, please check and correct both the test database and
the development database. It is important to keep the identical environment
between the test database and the development database.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||You can catch most issues by executing the proc with SET FMTONLY ON like the
example below (passing any needed parameters as NULL values). However, some
errors can only be found by actually executing the proc.
SET FMTONLY ON
GO
EXEC dbo.SP1
GO
SET FMTONLY OFF
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs,
> UDFs,..)
> are valid after developers updates but by simply running the script to
> create
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier

compile SP after creation?

Hello
Is it possible to force SQL Server to compile stored procedures after
creation? I mean, when I execute something like:
CREATE PROC SP1 AS
select * from X
where table X does not exist, I want to get error or warning. Now the proc
is created and the error is thrown only when it is executed. We have more
then 1500 stored procedures in the database now scripted out into sql
scripts. We want to ensure that scripts (and all objects like SPs, UDFs,..)
are valid after developers updates but by simply running the script to create
DB and all its content does not produce any warning/error. How can we
accomplish that?
Thanks
eXavierHi
i can think about adding IF OBJECT_ID('Table') IS NULL which means if the
NULL then object does not exist and your error will be thrown
"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs,
> UDFs,..)
> are valid after developers updates but by simply running the script to
> create
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier|||Hi,
I'm not sure if I understand what you mean. Do you mean to write some sql
script parser, extract all table names from all stored procedures and then
call your IF check? This is not acceptable for us. In fact we want the server
to compile procedures to get eventual errors.. SP is compiled when executed
first but we cannot automatically execute them all as they have different
parameters..
Any other idea?
Thanks
eXavier
"Uri Dimant" wrote:
> Hi
> i can think about adding IF OBJECT_ID('Table') IS NULL which means if the
> NULL then object does not exist and your error will be thrown
>
>
> "eXavier" <eXavier@.community.nospam> wrote in message
> news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> > Hello
> > Is it possible to force SQL Server to compile stored procedures after
> > creation? I mean, when I execute something like:
> >
> > CREATE PROC SP1 AS
> > select * from X
> >
> > where table X does not exist, I want to get error or warning. Now the proc
> > is created and the error is thrown only when it is executed. We have more
> > then 1500 stored procedures in the database now scripted out into sql
> > scripts. We want to ensure that scripts (and all objects like SPs,
> > UDFs,..)
> > are valid after developers updates but by simply running the script to
> > create
> > DB and all its content does not produce any warning/error. How can we
> > accomplish that?
> >
> > Thanks
> > eXavier
>
>|||Specify WITH RECOMPILE in your stored procedure. The procedure will not be
cached and will recompile at runtime.
--
MG
"eXavier" wrote:
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs, UDFs,..)
> are valid after developers updates but by simply running the script to create
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier|||Hi,
WITH RECOMPILE does exactly what you wrote. But it does not help in creation
time - you can still create procedure referencing non-existant tables.
The error is thrown only when the proc is executed, coming back to our
problem. (Further, if the SP compiles we want it to be cached.)
"MGeles" wrote:
> Specify WITH RECOMPILE in your stored procedure. The procedure will not be
> cached and will recompile at runtime.
> --
> MG
>
> "eXavier" wrote:
> > Hello
> > Is it possible to force SQL Server to compile stored procedures after
> > creation? I mean, when I execute something like:
> >
> > CREATE PROC SP1 AS
> > select * from X
> >
> > where table X does not exist, I want to get error or warning. Now the proc
> > is created and the error is thrown only when it is executed. We have more
> > then 1500 stored procedures in the database now scripted out into sql
> > scripts. We want to ensure that scripts (and all objects like SPs, UDFs,..)
> > are valid after developers updates but by simply running the script to create
> > DB and all its content does not produce any warning/error. How can we
> > accomplish that?
> >
> > Thanks
> > eXavier|||I'm afraid you cannot do that
"eXavier" <eXavier@.community.nospam> wrote in message
news:4BBE6A41-D987-4A5C-A953-1BD71A01A972@.microsoft.com...
> Hi,
> I'm not sure if I understand what you mean. Do you mean to write some sql
> script parser, extract all table names from all stored procedures and then
> call your IF check? This is not acceptable for us. In fact we want the
> server
> to compile procedures to get eventual errors.. SP is compiled when
> executed
> first but we cannot automatically execute them all as they have different
> parameters..
> Any other idea?
> Thanks
> eXavier
> "Uri Dimant" wrote:
>> Hi
>> i can think about adding IF OBJECT_ID('Table') IS NULL which means if
>> the
>> NULL then object does not exist and your error will be thrown
>>
>>
>> "eXavier" <eXavier@.community.nospam> wrote in message
>> news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
>> > Hello
>> > Is it possible to force SQL Server to compile stored procedures after
>> > creation? I mean, when I execute something like:
>> >
>> > CREATE PROC SP1 AS
>> > select * from X
>> >
>> > where table X does not exist, I want to get error or warning. Now the
>> > proc
>> > is created and the error is thrown only when it is executed. We have
>> > more
>> > then 1500 stored procedures in the database now scripted out into sql
>> > scripts. We want to ensure that scripts (and all objects like SPs,
>> > UDFs,..)
>> > are valid after developers updates but by simply running the script to
>> > create
>> > DB and all its content does not produce any warning/error. How can we
>> > accomplish that?
>> >
>> > Thanks
>> > eXavier
>>|||"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation?
Wayyy back in the day (SQL 6.5 I believe) this was possible, since SQL
checked for the existence of objects before creating stored procedures.
Apparently this caused a bunch of headaches, especially with database object
scripts that weren't created in the proper order, so "deferred name
resolution" was introduced. Table names are not resolved until run-time
instead of at creation time. Your best bet might be to 1) Do what Uri
suggested and explicitly check for the existence of tables yourself before
running the CREATE SP statement, or 2) Do what MS suggests below and test
your code after creation. Uri's suggestion should not be all that
difficult. Maybe a single script that tests for the existence of *all*
tables that are supposed to be in the database. You could run this script
before you try to run any SP creation scripts, and abort the mission based
on the result.
Here's what MS has to say on deferred name resolution:
"Deferred Name Resolution. Deferred name resolution allows procedure
compilation without all table references being present. Deferred name
resolution works in much the same way as the object-oriented concept of late
binding. At compile time, the compiler attempts to resolve all table names
that the procedure references. But if a table does not yet exist, the
compiler defers this name resolution until execution time.
For developers who have used temporary tables within their stored procedures
or triggers, this subtle new feature is long overdue. Although this feature
is useful, it has a side effect that many developers might initially
miss-the compiler no longer reliably catches table-name typos. Yes, you now
must test your code. This statement might sound funny at first, but if you
are not aware of deferred name resolution, you might find yourself wondering
why the compiler missed this error."
(From
http://www.microsoft.com/technet/prodtechnol/sql/70/deploy/migrat.mspx)|||Hi eXavier,
xyz's suggestion is reasonable. Appreciate your understanding that this
requirement is individual and actually limited by the design of SQL Server.
It is impossible for us to change the native behavior. I noticed that you
just wanted to ensure that scripts are valid after developers updates but
by simply running the script to create DB and all its content does not
produce any warning/error. You may consider to find a way from management.
For example, setup a test database environment which is same as the
development database then script a file to execute all the SPs or UDFs
that you want to check. This may require a standard process that the
developer of a SP or a UDF should provide a SQL test statement which can be
directly copied into the script file for checking at runtime. If some
errors are thrown out, please check and correct both the test database and
the development database. It is important to keep the identical environment
between the test database and the development database.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||You can catch most issues by executing the proc with SET FMTONLY ON like the
example below (passing any needed parameters as NULL values). However, some
errors can only be found by actually executing the proc.
SET FMTONLY ON
GO
EXEC dbo.SP1
GO
SET FMTONLY OFF
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs,
> UDFs,..)
> are valid after developers updates but by simply running the script to
> create
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier

compile a store proc again

Hi there!!

Can we force stored procs in a database to recompile and see if there is any compile time error in the stored procs then and there.

Something like sp_recompile which marks Stored Proc for recompilation and compilation occurs later on.

I am looking for something which will force the existing stored procs to compile and let me see the error if any.

Best Regards

Rahul Kumar, Software Engineer, India

SP_RECOMPILE is helpful to recompile the next time they are run.

It will remove all the chache Plan for the current object when you run the SP it will compile it.

You can do the following step to get the compile errors...

Exec Sp_recompile 'MySp'
Exec @.R = MySp
If @.R = 0
Print 'Success'
Else
Print 'Failed on Compile'

But the drawback here is you can't find the error is occured by compiler or runtime execution.

|||

This can be done for one stored proc, but we cant have this approach if we want to recompile all the strored procs in the database- as they may be having different parameters.

|||

You can try calling DBCC FREEPROCCACHE

This will flush the procedure cache and the cached plans. Next time you execute the procedures, they will compile and you should be able to hit the compilation error.

|||

It all depends on what you mean by "recompile". I know that I was really keen that the meant what it sounded like, which would be to take the source code, build an executable module, then a plan, and get things ready.

When you run sp_recompile, all it does is bump an internal value that is used to tell objects to recompile because something has changed. It will not take existing source code and rebuild the executable.

What is the purpose you are trying to achieve? Find where structures have changed? This is something you need to do with source control. Either by having documentation you can search (using a tool, or just manually searching) or just rebuild your project from scratch. Even recompiling is not foolproof unfortunately because of delayed name resolution, meaning that, in a procedure, if it comes to a table name that does not exist, it assumes that you know what you are doing and that the object will be created later. Man, I hate delayed name resolution, except when I need it (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490)

|||

Okay let me be bit specific what I am trying to achieve.--will also explain what i mean by "recompile"

I have a database in Sql Server 2000 and I am trying to copy it on Sql server 2005.Now we have many changes like sort of =* join which we have to do in stored procs.So I am trying to get list of stored procs which wont compile successfully on sql server 2005.

One way to get this list is with SQL Server 2005 Ugrade Advisor, I am surprise but it didnt give me an exhaustive list.

So I thought I would be better if I could recompile all the stored procs, and that what i am trying to achieve.

Best Regards

Rahul

|||

For that you would need to script out the stored procedures, and try to compile them. As long as object names are still the same (mitigating the whole question of delayed name resolution) then that will work.

If you know the pattern you are looking for, like =* or *=, you could use a search to find these cases:

DECLARE @.value nvarchar(128)
SET @.value = '=*'
SELECT cast(schema_name(schema_id) + '.' + name AS varchar(60)) AS name,
cast(type_desc AS varchar(20)) AS type , create_date,
modify_date,
char(13) + char(10)
+ '--select object_definition(' + cast(object_id as varchar(10)) + ') as [' + name + ']'
FROM sys.objects
WHERE charindex(@.value,replace(replace(object_definition(object_id),'[',''),']','')) > 0

--http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1139.entry

Object_definition is a new function in 2005 that gives you the text of the object in a nice package.

But, no, there is no way to have this kind of syntax check done automatically for you if the Upgrade Advisor misses it.

|||

Thanks Louis

this '=*' was just one issue I cited for example, there are and can (which i dont know) many more, which give error on sql server 2005 and even SQL Server 2005 Ugrade Advisor didnt catch.

Can we rewrite your given code as

select object_name(id) from syscomments where text like '%=*%'

@. Louis -- But, no, there is no way to have this kind of syntax check done automatically for you if the Upgrade Advisor misses it.

Does this mean that we can not identify affected stored proc if Upgrade Advisor misses them, expect by executing them.

Regards

Rahul

|||Install the SQL Server Best Practices Analyzer and run it against your SQL Server 2000 database. This has a rule to check for the older-style outer joins.|||

Yeah, anything that the program Umachandar mentions or the upgrade advisor miss will not be found. I was just giving an alternative for searching through your code if you find things you need to.

And no, syscomments cannot be relied upon for a precise search, since it is chunked into 4000 character chunks. Object_definition returns the full text as a varchar(max) that you can use the like on.

My experience iwth the Upgrade Advisor was pretty pleasant. We had very little trouble taking our 2000 databases from 2005, but then again, we try to keep up and usually make those kind of changes ahead of time (not that that helps you :)

|||

Just to site one example which made me wondering--

Here is a line of code from my stored proc

tsequal(TmStp, convert(varbinary(8), convert(bigint, @.xTmStp)))

Now the irony is my stored proc is got compiled in sql server 2000 and when i moved it to sql server 2005 and compiled it ther it gave me following error:-

Msg 102, Level 15, State 1, Procedure rsp_UpdPolSchdTmStp, Line 43

Incorrect syntax near 'TSEQUAL'.

Regards

Rahul Kumar

|||

Yep, TSEQUAL is gone for good in 2005 (http://sqljunkies.com/Forums/ShowPost.aspx?PostID=2534) but it wasn't documented for quite a while.

It isn't necessary, so you can change:

tsequal(TmStp, convert(varbinary(8), convert(bigint, @.xTmStp)))

to

TmStp = convert(varbinary(8), convert(bigint, @.xTmStp)))

though I am not sure why you are doing all of the converting. if @.xTmStp is of varbinary(8) type (or rowversion/timestamp type) you can just do:

TmStp = @.xTmStp

compile a store proc again

Hi there!!

Can we force stored procs in a database to recompile and see if there is any compile time error in the stored procs then and there.

Something like sp_recompile which marks Stored Proc for recompilation and compilation occurs later on.

I am looking for something which will force the existing stored procs to compile and let me see the error if any.

Best Regards

Rahul Kumar, Software Engineer, India

SP_RECOMPILE is helpful to recompile the next time they are run.

It will remove all the chache Plan for the current object when you run the SP it will compile it.

You can do the following step to get the compile errors...

Exec Sp_recompile 'MySp'
Exec @.R = MySp
If @.R = 0
Print 'Success'
Else
Print 'Failed on Compile'

But the drawback here is you can't find the error is occured by compiler or runtime execution.

|||

This can be done for one stored proc, but we cant have this approach if we want to recompile all the strored procs in the database- as they may be having different parameters.

|||

You can try calling DBCC FREEPROCCACHE

This will flush the procedure cache and the cached plans. Next time you execute the procedures, they will compile and you should be able to hit the compilation error.

|||

It all depends on what you mean by "recompile". I know that I was really keen that the meant what it sounded like, which would be to take the source code, build an executable module, then a plan, and get things ready.

When you run sp_recompile, all it does is bump an internal value that is used to tell objects to recompile because something has changed. It will not take existing source code and rebuild the executable.

What is the purpose you are trying to achieve? Find where structures have changed? This is something you need to do with source control. Either by having documentation you can search (using a tool, or just manually searching) or just rebuild your project from scratch. Even recompiling is not foolproof unfortunately because of delayed name resolution, meaning that, in a procedure, if it comes to a table name that does not exist, it assumes that you know what you are doing and that the object will be created later. Man, I hate delayed name resolution, except when I need it (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490)

|||

Okay let me be bit specific what I am trying to achieve.--will also explain what i mean by "recompile"

I have a database in Sql Server 2000 and I am trying to copy it on Sql server 2005.Now we have many changes like sort of =* join which we have to do in stored procs.So I am trying to get list of stored procs which wont compile successfully on sql server 2005.

One way to get this list is with SQL Server 2005 Ugrade Advisor, I am surprise but it didnt give me an exhaustive list.

So I thought I would be better if I could recompile all the stored procs, and that what i am trying to achieve.

Best Regards

Rahul

|||

For that you would need to script out the stored procedures, and try to compile them. As long as object names are still the same (mitigating the whole question of delayed name resolution) then that will work.

If you know the pattern you are looking for, like =* or *=, you could use a search to find these cases:

DECLARE @.value nvarchar(128)
SET @.value = '=*'
SELECT cast(schema_name(schema_id) + '.' + name AS varchar(60)) AS name,
cast(type_desc AS varchar(20)) AS type , create_date,
modify_date,
char(13) + char(10)
+ '--select object_definition(' + cast(object_id as varchar(10)) + ') as [' + name + ']'
FROM sys.objects
WHERE charindex(@.value,replace(replace(object_definition(object_id),'[',''),']','')) > 0

--http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1139.entry

Object_definition is a new function in 2005 that gives you the text of the object in a nice package.

But, no, there is no way to have this kind of syntax check done automatically for you if the Upgrade Advisor misses it.

|||

Thanks Louis

this '=*' was just one issue I cited for example, there are and can (which i dont know) many more, which give error on sql server 2005 and even SQL Server 2005 Ugrade Advisor didnt catch.

Can we rewrite your given code as

select object_name(id) from syscomments where text like '%=*%'

@. Louis -- But, no, there is no way to have this kind of syntax check done automatically for you if the Upgrade Advisor misses it.

Does this mean that we can not identify affected stored proc if Upgrade Advisor misses them, expect by executing them.

Regards

Rahul

|||Install the SQL Server Best Practices Analyzer and run it against your SQL Server 2000 database. This has a rule to check for the older-style outer joins.|||

Yeah, anything that the program Umachandar mentions or the upgrade advisor miss will not be found. I was just giving an alternative for searching through your code if you find things you need to.

And no, syscomments cannot be relied upon for a precise search, since it is chunked into 4000 character chunks. Object_definition returns the full text as a varchar(max) that you can use the like on.

My experience iwth the Upgrade Advisor was pretty pleasant. We had very little trouble taking our 2000 databases from 2005, but then again, we try to keep up and usually make those kind of changes ahead of time (not that that helps you :)

|||

Just to site one example which made me wondering--

Here is a line of code from my stored proc

tsequal(TmStp, convert(varbinary(8), convert(bigint, @.xTmStp)))

Now the irony is my stored proc is got compiled in sql server 2000 and when i moved it to sql server 2005 and compiled it ther it gave me following error:-

Msg 102, Level 15, State 1, Procedure rsp_UpdPolSchdTmStp, Line 43

Incorrect syntax near 'TSEQUAL'.

Regards

Rahul Kumar

|||

Yep, TSEQUAL is gone for good in 2005 (http://sqljunkies.com/Forums/ShowPost.aspx?PostID=2534) but it wasn't documented for quite a while.

It isn't necessary, so you can change:

tsequal(TmStp, convert(varbinary(8), convert(bigint, @.xTmStp)))

to

TmStp = convert(varbinary(8), convert(bigint, @.xTmStp)))

though I am not sure why you are doing all of the converting. if @.xTmStp is of varbinary(8) type (or rowversion/timestamp type) you can just do:

TmStp = @.xTmStp