Compile different when creating a view? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Compile different when creating a view?

Author

Topic

Movak
Starting Member

11 Posts

Posted - 2010-04-30 : 17:37:15

I have a stored procedure as follows:
DROP PROCEDURE sp_get_exception_report
GO

CREATE PROCEDURE sp_get_exception_report
@starttime nvarchar(100),
@endtime nvarchar(100),
@exception_idx int,
@max_records int = NULL,
@orderAscending bit = 0
AS

DECLARE @where_clause nvarchar(1000)
DECLARE @SQLString nvarchar(4000)
DECLARE @order nvarchar(4)
DECLARE @void_nosale bit
DECLARE @and as bit
DECLARE @conjunction as nvarchar(5)
DECLARE @temp_table as nvarchar(20)

SET @temp_table = N'test'
IF @orderAscending = 1
SELECT @order = 'ASC'
ELSE
SELECT @order = 'DESC'
SELECT @void_nosale = (SELECT void_nosale_transactions from Exception WHERE exception_idx = @exception_idx)
SELECT @where_clause = (SELECT where_clause from Exception WHERE exception_idx = @exception_idx)
IF @void_nosale = 1
BEGIN
print 'here'
SELECT @and = (SELECT filter_conjunction_AND from exception WHERE exception_idx = @exception_idx)
IF @and = 1
BEGIN
SELECT @conjunction = N' AND '
END
ELSE
BEGIN
SELECT @conjunction = N' OR '
END
EXEC sp_query_nosale_void @starttime,@endtime,'-1', 0, @temp_table OUTPUT

--SET @where_clause = N'(' + @where_clause + @conjunction + N'((receipt_no in (select receipt_nosale as receipt from ' + @temp_table + N' union select receipt_void as receipt from ' + @temp_table + N')) OR '
--SET @where_clause = @where_clause + N'(receipt_no IN (SELECT receipt_no FROM posdata WHERE void = 1 AND (receipt_no IN (SELECT receipt_no FROM posdata WHERE nosale = 1))))))'
END

SELECT @SQLString = N'SELECT ' + COALESCE(N'TOP ' + CAST(@max_records AS nvarchar(20)), N'') + ' * FROM POSData WITH (INDEX(ix_posdata_server_time)) WHERE ' + CAST(@where_clause AS nvarchar(1000)) + N' AND server_time >= ''' + @starttime + N''' AND server_time < ''' + @endtime + N''' ORDER BY server_time ' + @order + ', idx ' + @order
print @SQLString
EXEC sp_executesql @SQLString

RETURN @@ROWCOUNT

GO

When I run it with the following command I get a valid record set:
EXEC [Vigil-POS].[dbo].[sp_get_exception_report] '2010-04-30 10:49:11', '2010-04-30 11:49:11', 1, NULL, 1

When I run it with the following command I get a compile error:
CREATE VIEW [vw_tmp_51148D90] AS EXEC [Vigil-POS].[dbo].[sp_get_exception_report] '2010-04-30 10:49:11', '2010-04-30 11:49:11', 1, NULL, 1

The error is:
Server: Msg 156, Level 15, State 1, Procedure vw_tmp_51148D90, Line 1
Incorrect syntax near the keyword 'EXEC'.

It looks like it compiles differently when I create a view. Any help would be appreciated.

Thanks,
Joe

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-30 : 20:27:45

You can't put a stored procedure inside a view. A view can only contain a SELECT query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2010-05-01 : 02:41:33

By the by, best not to start your stored procedures names with "sp_" - if they do then SQL will check if the name exists in MASTER database first, so that adds time to every execution

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-01 : 03:28:10

can i ask the intention behind putting procedure inside view?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-05-01 : 03:55:54

There are a number of other things that concern me:

Use of sp_ExecuteSQL without passing parameters to sp_ExecuteSQL - the benefit of the query plan being cahced (which is very significant) will be lost

Forcing the index to be used is very unusual, and should be avoided other than very exceptional circumstances

"from Exception WHERE exception_idx = @exception_idx" used three times to set three separate variables, which could be done in one statement instead

Movak
Starting Member

11 Posts

Posted - 2010-05-01 : 15:20:08

I am very new to stored procedures and the code I am working with.
I have found that the call attempting to create the view from the stored procedure silently fails and then tries again without using a view.

The multiple calls using "from Exception WHERE exception_idx = @exception_idx" multiple time is due to my lack of knowledge of how to update multiple variable from one call.

I will look into the forcing of indexed and parameter passing to sp_ExecuteSQL. Links would be helpful.

Is there any documentation about using sp_ as the start of the procedure name that I can show to my boss?

Thanks for all the help.

Kristen
Test

22859 Posts

Posted - 2010-05-02 : 03:58:18

"I will look into the forcing of indexed and parameter passing to sp_ExecuteSQL. Links would be helpful"

Have a look in the SQL DOCs ("Books Online")

Might be a bit heavy going if you are a Newbie, but this article is excellent
http://www.sommarskog.se/dynamic_sql.html#sp_executesql

"Is there any documentation about using sp_ as the start of the procedure name that I can show to my boss?"

Yeah, look in the SQL DOCs ("Books Online")

under "CREATE PROCEDURE"

Movak
Starting Member

11 Posts

Posted - 2010-05-03 : 14:11:08

I have discovered that the actual command that it is running is as follows;
bcp "EXEC [Vigil-POS].[dbo].[sp_get_exception_report] '2010-04-21 08:50:14', '2010-05-03 09:50:14', 2, NULL, 1" queryout "C:\DOCUME~1\josephk\LOCALS~1\Temp\csi248.tmp" -c -CACP -b1000 -S127.0.0.1,1433 -Uvpos -Pvpos -t -r

The temp file only contains the first column of the table and nothing else. If I comment out the call the sp_query_nosale_void stored procedure it returns the full row.

Any help would be appreciated

Kristen
Test

22859 Posts

Posted - 2010-05-03 : 15:01:32

Probably a minor point, but if you want an unambiguous date use ONLY the format 'yyyymmdd' (no hyphens) - e.g. '20100421' and if you want the time as well you ONLY the format 'yyyy-mm-ddThh:mm:ss' (hyphens are required) e.g. '2010-04-21T08:50:14'

Any other format is ambiguous and will be interpreted according to the locale of your server, and your personal SQL settings - such as Country / Language

What happens if you run the command:


EXEC [Vigil-POS].[dbo].[sp_get_exception_report] '2010-04-21 08:50:14', '2010-05-03 09:50:14', 2, NULL, 1

do you get the same output as the file?

and what about:


EXEC [Vigil-POS].[dbo].[sp_get_exception_report] '2010-04-21T08:50:14', '2010-05-03T09:50:14', 2, NULL, 1

Movak
Starting Member

11 Posts

Posted - 2010-05-03 : 18:00:04

It made no difference

Kristen
Test

22859 Posts

Posted - 2010-05-04 : 02:56:50

Yeah, didn't think it would as you most probably would have got very different results if date parsing had been the issue. Something to keep in mind though in case SQL settings change, or the database is moved to a different server etc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources