Part

Hi folks,
I posted this somewhere else here, excuse me,,,
I have the following challenge
When I create a stored procedure the selects data from the portioned view,
the execution plan shows that SQL server scans all base tables, while the
select statement after replacing the variable with an actual value scans onl
y
the required table.
Here is a full script:
Execute the commented out statements at the end of the script with show
graphical execution plan option and see the difference.
SET NOCOUNT ON
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[EMPLOYEES]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[EMPLOYEES]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees_1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees_1]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees_2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees_2]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees_3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees_3]
GO
CREATE TABLE [Employees_1] (
[EmpID] [int] NOT NULL ,
[EmpName] [char] (5) NULL ,
[FirstChar] [int] NOT NULL ,
CONSTRAINT [PK_Employees_1] PRIMARY KEY CLUSTERED
(
[EmpID],
[FirstChar]
) ON [PRIMARY] ,
CONSTRAINT [CK_Employees_1] CHECK ([FirstChar] = 1)
) ON [PRIMARY]
CREATE TABLE [Employees_2] (
[EmpID] [int] NOT NULL ,
[EmpName] [char] (5) NULL ,
[FirstChar] [int] NOT NULL ,
CONSTRAINT [PK_Employees_2] PRIMARY KEY CLUSTERED
(
[EmpID],
[FirstChar]
) ON [PRIMARY] ,
CONSTRAINT [CK_Employees_2] CHECK ([FirstChar] = 2)
) ON [PRIMARY]
CREATE TABLE [Employees_3] (
[EmpID] [int] NOT NULL ,
[EmpName] [char] (5) NULL ,
[FirstChar] [int] NOT NULL ,
CONSTRAINT [PK_Employees_3] PRIMARY KEY CLUSTERED
(
[EmpID],
[FirstChar]
) ON [PRIMARY] ,
CONSTRAINT [CK_Employees_3] CHECK ([FirstChar] = 3)
) ON [PRIMARY]
GO
--
CREATE VIEW EMPLOYEES
AS
SELECT *
FROM EMPLOYEES_1
UNION ALL
SELECT *
FROM EMPLOYEES_2
UNION ALL
SELECT *
FROM EMPLOYEES_3
GO
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (1,'aaaaa',1)
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (2,'aaaab',1)
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (3,'baaaa',2)
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (4,'baaab',2)
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (5,'caaaa',3)
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (6,'caaab',3)
go
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[GetEmp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetEmp]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[GetEmp2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetEmp2]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc GetEmp
@.firstchar int
as
select * from employees
where (EMPNAME IS NOT NULL)
and firstchar = @.firstchar
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc GetEmp2
@.firstchar int
as
declare @.sql varchar(8000)
set @.sql ='
select * from employees
where (EMPNAME IS NOT NULL)
and firstchar = ' + convert(varchar,@.firstchar)
exec (@.sql)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/*
select * from employees
where (EMPNAME IS NOT NULL)
and firstchar = 2
exec GetEmp 2
exec GetEmp2 2
*/Use STATISTICS IO and you will see that SQL Server will only access one of t
he table for the GetEmp
procedure. But the plan doesn't show that because the same plan should work
for whatever value you
send in the parameter. So elimination of the other views are deferred until
run-time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RATA" <RATA@.discussions.microsoft.com> wrote in message
news:B68CA9C0-02B9-4A55-8904-B7DC3EBE727C@.microsoft.com...
> Hi folks,
> I posted this somewhere else here, excuse me,,,
> I have the following challenge
> When I create a stored procedure the selects data from the portioned view,
> the execution plan shows that SQL server scans all base tables, while the
> select statement after replacing the variable with an actual value scans o
nly
> the required table.
> Here is a full script:
> Execute the commented out statements at the end of the script with show
> graphical execution plan option and see the difference.
>
> SET NOCOUNT ON
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EMPLOYEES]') and OBJECTPROPERTY(id, N'IsView') = 1)
> drop view [dbo].[EMPLOYEES]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Employees_1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Employees_1]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Employees_2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Employees_2]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Employees_3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Employees_3]
> GO
> CREATE TABLE [Employees_1] (
> [EmpID] [int] NOT NULL ,
> [EmpName] [char] (5) NULL ,
> [FirstChar] [int] NOT NULL ,
> CONSTRAINT [PK_Employees_1] PRIMARY KEY CLUSTERED
> (
> [EmpID],
> [FirstChar]
> ) ON [PRIMARY] ,
> CONSTRAINT [CK_Employees_1] CHECK ([FirstChar] = 1)
> ) ON [PRIMARY]
> CREATE TABLE [Employees_2] (
> [EmpID] [int] NOT NULL ,
> [EmpName] [char] (5) NULL ,
> [FirstChar] [int] NOT NULL ,
> CONSTRAINT [PK_Employees_2] PRIMARY KEY CLUSTERED
> (
> [EmpID],
> [FirstChar]
> ) ON [PRIMARY] ,
> CONSTRAINT [CK_Employees_2] CHECK ([FirstChar] = 2)
> ) ON [PRIMARY]
> CREATE TABLE [Employees_3] (
> [EmpID] [int] NOT NULL ,
> [EmpName] [char] (5) NULL ,
> [FirstChar] [int] NOT NULL ,
> CONSTRAINT [PK_Employees_3] PRIMARY KEY CLUSTERED
> (
> [EmpID],
> [FirstChar]
> ) ON [PRIMARY] ,
> CONSTRAINT [CK_Employees_3] CHECK ([FirstChar] = 3)
> ) ON [PRIMARY]
> GO
> --
> CREATE VIEW EMPLOYEES
> AS
> SELECT *
> FROM EMPLOYEES_1
> UNION ALL
> SELECT *
> FROM EMPLOYEES_2
> UNION ALL
> SELECT *
> FROM EMPLOYEES_3
> GO
>
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (1,'aaaaa',1)
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (2,'aaaab',1)
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (3,'baaaa',2)
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (4,'baaab',2)
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (5,'caaaa',3)
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (6,'caaab',3)
> go
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[GetEmp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[GetEmp]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[GetEmp2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[GetEmp2]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> create proc GetEmp
> @.firstchar int
> as
> select * from employees
> where (EMPNAME IS NOT NULL)
> and firstchar = @.firstchar
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE proc GetEmp2
> @.firstchar int
> as
> declare @.sql varchar(8000)
> set @.sql ='
> select * from employees
> where (EMPNAME IS NOT NULL)
> and firstchar = ' + convert(varchar,@.firstchar)
> exec (@.sql)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> /*
> select * from employees
> where (EMPNAME IS NOT NULL)
> and firstchar = 2
> exec GetEmp 2
> exec GetEmp2 2
> */
>|||Karaszi,
the SP takes time much more than the select stmt, this is why i analyzed the
execution plan.
"Tibor Karaszi" wrote:

> Use STATISTICS IO and you will see that SQL Server will only access one of
the table for the GetEmp
> procedure. But the plan doesn't show that because the same plan should wor
k for whatever value you
> send in the parameter. So elimination of the other views are deferred unti
l run-time.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "RATA" <RATA@.discussions.microsoft.com> wrote in message
> news:B68CA9C0-02B9-4A55-8904-B7DC3EBE727C@.microsoft.com...
>
>|||What does statistics IO say when you execute it? I assume that you have more
data in your tables
than the test script?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RATA" <RATA@.discussions.microsoft.com> wrote in message
news:CD107B8F-9B5B-4788-8FC1-5E25B321292D@.microsoft.com...
> Karaszi,
> the SP takes time much more than the select stmt, this is why i analyzed t
he
> execution plan.
> "Tibor Karaszi" wrote:
>|||I am sorry; there was a bug in my SP that was enforcing the engine to scan
all queries. My issue is closed now.
This posting was useful; you don’t have to rely on the graphical execution
plan always, some time you have to examine other thinks like the SET
STATISTICS IO.
Thanks all
"Tibor Karaszi" wrote:

> What does statistics IO say when you execute it? I assume that you have mo
re data in your tables
> than the test script?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RATA" <RATA@.discussions.microsoft.com> wrote in message
> news:CD107B8F-9B5B-4788-8FC1-5E25B321292D@.microsoft.com...
>

Parsing values from sp_spaceused stored proc.

I am using sp_spaceused stored procedure to get the database_size result.
The result comes back as a string such as "512 KB" or "100 MB". I really
need a number value representation of the size. I though about parsing the
string, but I was not sure if there were any other results of the string
that I may not account for. Is there any standard way to parse this string,
or should I just assume that the format of the string can either have KB or
MB at the end and parse it based on that assumption?
--
Ken Varn
Senior Software Engineer
Diebold Inc.
EmailID = varnk
Domain = Diebold.com
--You can look at the contents of the stored proc via the Enterprise Manager
or
via
use master
go
sp_Helptext sp_spaceused
I don't see any values represented other than KB and MB
"Ken Varn" <nospam> wrote in message
news:eXe6n$6WFHA.1468@.tk2msftngp13.phx.gbl...
> I am using sp_spaceused stored procedure to get the database_size result.
> The result comes back as a string such as "512 KB" or "100 MB". I really
> need a number value representation of the size. I though about parsing
the
> string, but I was not sure if there were any other results of the string
> that I may not account for. Is there any standard way to parse this
string,
> or should I just assume that the format of the string can either have KB
or
> MB at the end and parse it based on that assumption?
> --
> --
> Ken Varn
> Senior Software Engineer
> Diebold Inc.
> EmailID = varnk
> Domain = Diebold.com
> --
>|||Ken
You can create a table and store an output from sp_spaceused there.
If I understood you need the number only. So see if this helps you.
CREATE FUNCTION dbo.CleanChars
(@.str VARCHAR(8000), @.validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars +
']%',@.str), 1) ,'')
RETURN @.str
END
GO
CREATE TABLE sometable
(namestr VARCHAR(20) PRIMARY KEY)
INSERT INTO sometable VALUES ('AB-C123')
INSERT INTO sometable VALUES ('A,B,C')
SELECT namestr,
dbo.CleanChars(namestr,'A-Z 0-9')
FROM sometable
"Ken Varn" <nospam> wrote in message
news:eXe6n$6WFHA.1468@.tk2msftngp13.phx.gbl...
> I am using sp_spaceused stored procedure to get the database_size result.
> The result comes back as a string such as "512 KB" or "100 MB". I really
> need a number value representation of the size. I though about parsing
the
> string, but I was not sure if there were any other results of the string
> that I may not account for. Is there any standard way to parse this
string,
> or should I just assume that the format of the string can either have KB
or
> MB at the end and parse it based on that assumption?
> --
> --
> Ken Varn
> Senior Software Engineer
> Diebold Inc.
> EmailID = varnk
> Domain = Diebold.com
> --
>|||Ken,
To be sure, see the sp code from master database.
exec master..sp_helptext sp_spaceused
go
AMB
"Ken Varn" wrote:

> I am using sp_spaceused stored procedure to get the database_size result.
> The result comes back as a string such as "512 KB" or "100 MB". I really
> need a number value representation of the size. I though about parsing th
e
> string, but I was not sure if there were any other results of the string
> that I may not account for. Is there any standard way to parse this strin
g,
> or should I just assume that the format of the string can either have KB o
r
> MB at the end and parse it based on that assumption?
> --
> --
> Ken Varn
> Senior Software Engineer
> Diebold Inc.
> EmailID = varnk
> Domain = Diebold.com
> --
>
>

Parsing T-SQL is not validating table schemas

I am curious why my stored procedures are parsing properly when you do not reference a table with its schema. The stored procs then fail when you run them.

It seems that the parser does not validate that a tables schema is missing.

This is an example stored procedure against the Person.Address table in the Adventureworks database. It will execute fine if I change the FROM clause to Person.Address.

CREATE PROCEDURE [dbo].[Address_Load]
@.AddressID [int]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @.intError int

BEGIN TRY
SELECT A.[AddressID]
, A.[AddressLine1]
, A.[AddressLine2]
, A.[City]
, A.[StateProvinceID]
, A.[PostalCode]
FROM [Address] A
WHERE A.[AddressID] = @.AddressID

IF @.@.ROWCOUNT = 0
BEGIN
RAISERROR('Record not found', 16, 1) -- Record not found.
END

-- Return success
RETURN 0
END TRY
BEGIN CATCH
SET @.intError = ERROR_NUMBER();

-- Log error here

RETURN @.intError;
END CATCH
END

The stored proc parses fine and gets saved to the database but when executing it I get the following

Msg 208, Level 16, State 1, Procedure Address_Load, Line 10

Invalid object name 'Address'.

Is there any way to change this so the parsing will generate an error and not allow this into the database?

Thanks,

Cory

The behavior is due to deferred name resolution/compilation of TSQL modules in SQL Server. You can look it up in BOL for more details. See link below for starters:

http://msdn2.microsoft.com/en-us/library/ms190686.aspx

|||

And when you decide you don't like the behavior, please go here and vote!

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490

It is good about 1% of the time to be like this. However, the problem is the 2% of the time when you are coding and mistype a table name and it still compiles, only to find out later when you are (hopefully) testing :)

|||Is there a way to turn off Deferred Name Resolution?|||

Nope. That is what this feedback that I mentioned:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490

It really ought to be a settting you can ask for, not soemthing that is on all of the time.

Parse values from delimited string

Hi. I have a string like this which will be passed into a SQL Server 2000 stored procedure,

[15438|39][21347|96][24198|23]....

I need to take the values in the above string and insert them into a temp table so they will look like this. How can a delimited string be parsed into this? Thanks.

Exam ID Branch Number 15438 39 21347 96 24198 23

Look at the use of Jens' Split function, available here.

Seems like you need to split on the brackets, ][

and then again on the pipe, |

Split Function (Jens Suessmeyer)
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

|||The Split function returns a table. How would I call the function if it needs to split the string twice? An example is very much appreciated. Also, I'm thinking of passing the data this way to make it even simpler,

|23415^33|23451^23|....
|||

I recommand to use the XML input rather than the delimted string. You already have a structured string. Just replace the string as follow as and utilize the OPENXML; Since your schema is simple it will be simple and faster. But String manipulation will hit your performance badly(bcs you have to do split of split of split).

Sample with your input...

Code Snippet

Declare @.Data as Varchar(100);

Declare @.XML as Varchar(8000);

Set @.Data = '[15438|39][21347|96][24198|23]'

Set @.XML = Replace(Replace(Replace(@.Data,'[','<row><col1>'),'|','</col1><col2>'),']','</col2></row>')

select @.XML = '<Data>' + @.XML + '</Data>'

Declare @.iDoc as Int;

Exec sp_xml_preparedocument @.iDoc OUTPUT, @.XML

Select * From OpenXML(@.iDoc, 'Data/row', 2) With (col1 int, col2 int)

Exec sp_xml_removedocument @.iDoc

You can pass the input from your server as follow as. (Advantage: You can pass text datatype from your UI to database, but delimted values wont allow more than 8000 chars)

Code Snippet

<Root>

<row>

<Col1>15438</Col1>

<Col2>39</Col2>

</row>

<row>

<Col1>21347</Col1>

<Col2>96</Col2>

</row>

<row>

<Col1>24198</Col1>

<Col2>23</Col2>

</row>

</Root>

|||Since you can alter the input string format, I suggest that you use comma delimited, and follow Mani's suggestion about using XML.|||

Mani,

You're quite right. String manipulation is not good with T-SQL. (Unfortunately, shredding xml isn't so hot either...)

But in this case, it may be the lesser of the evils...

|||

Yes Arnie. Since the schema is simple OPENXML perform well.

In future if they want to migrate to SQL Server 2005, they can utilize the XQuery featuer. Where we can avoid the preparedocument.

|||

As a quick aside, did you verify the code you posted for the OP?

It didn't run for me...

|||Yes. All tags are got supressed. Corrected Now. Thank you. |||You should fix the client or application sending the data to send it in a different format (xml, fixed length blob) or call the SP multiple times. You can also dump the rows into a temporary table and process the rows in the SP. Doing string manipulations is slow and kludgy.

parse query

i am trying to write a stored procedure which parses the string query passed
as input and returns whether it is a valid statement or not
was trying to use "SET PARSEONLY ON" without any luck
thanks
red"Parseonly" does not parse for dynamic query. This is by design. Basically,
'parseonly' only parses for syntax and dynamic query is parsed at runtime.
-- this would parse fine
-- because @.sql is a valid variable
-- and exec(@.sql) syntactically correct
-- though this would err at runtime
set parseonly on
go
declare @.sql sysname
set @.sql='aflasfasfaslfsaf'
exec(@.sql)
--
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>i am trying to write a stored procedure which parses the string query
>passed
> as input and returns whether it is a valid statement or not
> was trying to use "SET PARSEONLY ON" without any luck
> thanks
> red|||is there any other way that i can make it to work
srinivas
"oj" wrote:
> "Parseonly" does not parse for dynamic query. This is by design. Basically,
> 'parseonly' only parses for syntax and dynamic query is parsed at runtime.
> -- this would parse fine
> -- because @.sql is a valid variable
> -- and exec(@.sql) syntactically correct
> -- though this would err at runtime
> set parseonly on
> go
> declare @.sql sysname
> set @.sql='aflasfasfaslfsaf'
> exec(@.sql)
> --
> -oj
>
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
> >i am trying to write a stored procedure which parses the string query
> >passed
> > as input and returns whether it is a valid statement or not
> >
> > was trying to use "SET PARSEONLY ON" without any luck
> >
> > thanks
> > red
>
>|||No.
--
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
> is there any other way that i can make it to work
> srinivas
> "oj" wrote:
>> "Parseonly" does not parse for dynamic query. This is by design.
>> Basically,
>> 'parseonly' only parses for syntax and dynamic query is parsed at
>> runtime.
>> -- this would parse fine
>> -- because @.sql is a valid variable
>> -- and exec(@.sql) syntactically correct
>> -- though this would err at runtime
>> set parseonly on
>> go
>> declare @.sql sysname
>> set @.sql='aflasfasfaslfsaf'
>> exec(@.sql)
>> --
>> -oj
>>
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >i am trying to write a stored procedure which parses the string query
>> >passed
>> > as input and returns whether it is a valid statement or not
>> >
>> > was trying to use "SET PARSEONLY ON" without any luck
>> >
>> > thanks
>> > red
>>|||Hi
You could exec it prepend with SET PARSEONLY ON?
DECLARE @.sql varchar(8000)
DECLARE @.errval int
SET @.sql = 'SELECT * FROM PUBS..Authors'
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
SET @.sql = 'SELECT * FROM '
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
SET @.sql = 'SEECT * FROM PUBS..Authors'
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
John
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
> is there any other way that i can make it to work
> srinivas
> "oj" wrote:
>> "Parseonly" does not parse for dynamic query. This is by design.
>> Basically,
>> 'parseonly' only parses for syntax and dynamic query is parsed at
>> runtime.
>> -- this would parse fine
>> -- because @.sql is a valid variable
>> -- and exec(@.sql) syntactically correct
>> -- though this would err at runtime
>> set parseonly on
>> go
>> declare @.sql sysname
>> set @.sql='aflasfasfaslfsaf'
>> exec(@.sql)
>> --
>> -oj
>>
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >i am trying to write a stored procedure which parses the string query
>> >passed
>> > as input and returns whether it is a valid statement or not
>> >
>> > was trying to use "SET PARSEONLY ON" without any luck
>> >
>> > thanks
>> > red
>>|||hi John
what if the table name doesnt exist in the database... say
"select * from authrs" instead of "select * from authors"....
your code still executes it( shouldnt the parse take care of that
too....please correct me if i am wrong)
thanks
red
"John Bell" wrote:
> Hi
> You could exec it prepend with SET PARSEONLY ON?
> DECLARE @.sql varchar(8000)
> DECLARE @.errval int
> SET @.sql = 'SELECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SELECT * FROM '
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SEECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> John
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
> > is there any other way that i can make it to work
> >
> > srinivas
> >
> > "oj" wrote:
> >
> >> "Parseonly" does not parse for dynamic query. This is by design.
> >> Basically,
> >> 'parseonly' only parses for syntax and dynamic query is parsed at
> >> runtime.
> >>
> >> -- this would parse fine
> >> -- because @.sql is a valid variable
> >> -- and exec(@.sql) syntactically correct
> >> -- though this would err at runtime
> >> set parseonly on
> >> go
> >> declare @.sql sysname
> >> set @.sql='aflasfasfaslfsaf'
> >> exec(@.sql)
> >>
> >> --
> >> -oj
> >>
> >>
> >>
> >> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> >> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
> >> >i am trying to write a stored procedure which parses the string query
> >> >passed
> >> > as input and returns whether it is a valid statement or not
> >> >
> >> > was trying to use "SET PARSEONLY ON" without any luck
> >> >
> >> > thanks
> >> > red
> >>
> >>
> >>
>
>|||Ah yes. If 'parseonly' is part of the statement. The entire string will get
parsed at runtime (i.e. exec()).
--
-oj
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23E1YRyrQFHA.2948@.TK2MSFTNGP14.phx.gbl...
> Hi
> You could exec it prepend with SET PARSEONLY ON?
> DECLARE @.sql varchar(8000)
> DECLARE @.errval int
> SET @.sql = 'SELECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SELECT * FROM '
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SEECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> John
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>> is there any other way that i can make it to work
>> srinivas
>> "oj" wrote:
>> "Parseonly" does not parse for dynamic query. This is by design.
>> Basically,
>> 'parseonly' only parses for syntax and dynamic query is parsed at
>> runtime.
>> -- this would parse fine
>> -- because @.sql is a valid variable
>> -- and exec(@.sql) syntactically correct
>> -- though this would err at runtime
>> set parseonly on
>> go
>> declare @.sql sysname
>> set @.sql='aflasfasfaslfsaf'
>> exec(@.sql)
>> --
>> -oj
>>
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >i am trying to write a stored procedure which parses the string query
>> >passed
>> > as input and returns whether it is a valid statement or not
>> >
>> > was trying to use "SET PARSEONLY ON" without any luck
>> >
>> > thanks
>> > red
>>
>|||well, parseonly only parses for sql well-formed/syntax. It does not check
for the object's existence.
DECLARE @.sql sysname
SET @.sql = 'SELECT blah '
EXEC ('SET PARSEONLY ON ' + @.SQL)
PRINT(@.@.ERROR)
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:BBD5F63C-9276-49E6-802D-F2A3E93B7791@.microsoft.com...
> hi John
> what if the table name doesnt exist in the database... say
> "select * from authrs" instead of "select * from authors"....
> your code still executes it( shouldnt the parse take care of that
> too....please correct me if i am wrong)
> thanks
> red
> "John Bell" wrote:
>> Hi
>> You could exec it prepend with SET PARSEONLY ON?
>> DECLARE @.sql varchar(8000)
>> DECLARE @.errval int
>> SET @.sql = 'SELECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SELECT * FROM '
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SEECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> John
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>> > is there any other way that i can make it to work
>> >
>> > srinivas
>> >
>> > "oj" wrote:
>> >
>> >> "Parseonly" does not parse for dynamic query. This is by design.
>> >> Basically,
>> >> 'parseonly' only parses for syntax and dynamic query is parsed at
>> >> runtime.
>> >>
>> >> -- this would parse fine
>> >> -- because @.sql is a valid variable
>> >> -- and exec(@.sql) syntactically correct
>> >> -- though this would err at runtime
>> >> set parseonly on
>> >> go
>> >> declare @.sql sysname
>> >> set @.sql='aflasfasfaslfsaf'
>> >> exec(@.sql)
>> >>
>> >> --
>> >> -oj
>> >>
>> >>
>> >>
>> >> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> >> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >> >i am trying to write a stored procedure which parses the string query
>> >> >passed
>> >> > as input and returns whether it is a valid statement or not
>> >> >
>> >> > was trying to use "SET PARSEONLY ON" without any luck
>> >> >
>> >> > thanks
>> >> > red
>> >>
>> >>
>> >>
>>|||Hi
Even with dynamic SQL your tables existance should not be in doubt,
otherwise you are almost certainly open to SQL injection
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
Also check out:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
John
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:BBD5F63C-9276-49E6-802D-F2A3E93B7791@.microsoft.com...
> hi John
> what if the table name doesnt exist in the database... say
> "select * from authrs" instead of "select * from authors"....
> your code still executes it( shouldnt the parse take care of that
> too....please correct me if i am wrong)
> thanks
> red
> "John Bell" wrote:
>> Hi
>> You could exec it prepend with SET PARSEONLY ON?
>> DECLARE @.sql varchar(8000)
>> DECLARE @.errval int
>> SET @.sql = 'SELECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SELECT * FROM '
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SEECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> John
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>> > is there any other way that i can make it to work
>> >
>> > srinivas
>> >
>> > "oj" wrote:
>> >
>> >> "Parseonly" does not parse for dynamic query. This is by design.
>> >> Basically,
>> >> 'parseonly' only parses for syntax and dynamic query is parsed at
>> >> runtime.
>> >>
>> >> -- this would parse fine
>> >> -- because @.sql is a valid variable
>> >> -- and exec(@.sql) syntactically correct
>> >> -- though this would err at runtime
>> >> set parseonly on
>> >> go
>> >> declare @.sql sysname
>> >> set @.sql='aflasfasfaslfsaf'
>> >> exec(@.sql)
>> >>
>> >> --
>> >> -oj
>> >>
>> >>
>> >>
>> >> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> >> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >> >i am trying to write a stored procedure which parses the string query
>> >> >passed
>> >> > as input and returns whether it is a valid statement or not
>> >> >
>> >> > was trying to use "SET PARSEONLY ON" without any luck
>> >> >
>> >> > thanks
>> >> > red
>> >>
>> >>
>> >>
>>

parse query

i am trying to write a stored procedure which parses the string query passed
as input and returns whether it is a valid statement or not
was trying to use "SET PARSEONLY ON" without any luck
thanks
red
"Parseonly" does not parse for dynamic query. This is by design. Basically,
'parseonly' only parses for syntax and dynamic query is parsed at runtime.
-- this would parse fine
-- because @.sql is a valid variable
-- and exec(@.sql) syntactically correct
-- though this would err at runtime
set parseonly on
go
declare @.sql sysname
set @.sql='aflasfasfaslfsaf'
exec(@.sql)
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>i am trying to write a stored procedure which parses the string query
>passed
> as input and returns whether it is a valid statement or not
> was trying to use "SET PARSEONLY ON" without any luck
> thanks
> red
|||is there any other way that i can make it to work
srinivas
"oj" wrote:

> "Parseonly" does not parse for dynamic query. This is by design. Basically,
> 'parseonly' only parses for syntax and dynamic query is parsed at runtime.
> -- this would parse fine
> -- because @.sql is a valid variable
> -- and exec(@.sql) syntactically correct
> -- though this would err at runtime
> set parseonly on
> go
> declare @.sql sysname
> set @.sql='aflasfasfaslfsaf'
> exec(@.sql)
> --
> -oj
>
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>
>
|||No.
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...[vbcol=seagreen]
> is there any other way that i can make it to work
> srinivas
> "oj" wrote:
|||Hi
You could exec it prepend with SET PARSEONLY ON?
DECLARE @.sql varchar(8000)
DECLARE @.errval int
SET @.sql = 'SELECT * FROM PUBS..Authors'
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
SET @.sql = 'SELECT * FROM '
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
SET @.sql = 'SEECT * FROM PUBS..Authors'
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
John
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...[vbcol=seagreen]
> is there any other way that i can make it to work
> srinivas
> "oj" wrote:
|||hi John
what if the table name doesnt exist in the database... say
"select * from authrs" instead of "select * from authors"....
your code still executes it( shouldnt the parse take care of that
too....please correct me if i am wrong)
thanks
red
"John Bell" wrote:

> Hi
> You could exec it prepend with SET PARSEONLY ON?
> DECLARE @.sql varchar(8000)
> DECLARE @.errval int
> SET @.sql = 'SELECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SELECT * FROM '
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SEECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> John
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>
>
|||Ah yes. If 'parseonly' is part of the statement. The entire string will get
parsed at runtime (i.e. exec()).
-oj
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23E1YRyrQFHA.2948@.TK2MSFTNGP14.phx.gbl...
> Hi
> You could exec it prepend with SET PARSEONLY ON?
> DECLARE @.sql varchar(8000)
> DECLARE @.errval int
> SET @.sql = 'SELECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SELECT * FROM '
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SEECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> John
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>
|||well, parseonly only parses for sql well-formed/syntax. It does not check
for the object's existence.
DECLARE @.sql sysname
SET @.sql = 'SELECT blah '
EXEC ('SET PARSEONLY ON ' + @.SQL)
PRINT(@.@.ERROR)
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:BBD5F63C-9276-49E6-802D-F2A3E93B7791@.microsoft.com...[vbcol=seagreen]
> hi John
> what if the table name doesnt exist in the database... say
> "select * from authrs" instead of "select * from authors"....
> your code still executes it( shouldnt the parse take care of that
> too....please correct me if i am wrong)
> thanks
> red
> "John Bell" wrote:
|||Hi
Even with dynamic SQL your tables existance should not be in doubt,
otherwise you are almost certainly open to SQL injection
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
Also check out:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
John
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:BBD5F63C-9276-49E6-802D-F2A3E93B7791@.microsoft.com...[vbcol=seagreen]
> hi John
> what if the table name doesnt exist in the database... say
> "select * from authrs" instead of "select * from authors"....
> your code still executes it( shouldnt the parse take care of that
> too....please correct me if i am wrong)
> thanks
> red
> "John Bell" wrote:

parse query

i am trying to write a stored procedure which parses the string query passed
as input and returns whether it is a valid statement or not
was trying to use "SET PARSEONLY ON" without any luck
thanks
red"Parseonly" does not parse for dynamic query. This is by design. Basically,
'parseonly' only parses for syntax and dynamic query is parsed at runtime.
-- this would parse fine
-- because @.sql is a valid variable
-- and exec(@.sql) syntactically correct
-- though this would err at runtime
set parseonly on
go
declare @.sql sysname
set @.sql='aflasfasfaslfsaf'
exec(@.sql)
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>i am trying to write a stored procedure which parses the string query
>passed
> as input and returns whether it is a valid statement or not
> was trying to use "SET PARSEONLY ON" without any luck
> thanks
> red|||is there any other way that i can make it to work
srinivas
"oj" wrote:

> "Parseonly" does not parse for dynamic query. This is by design. Basically
,
> 'parseonly' only parses for syntax and dynamic query is parsed at runtime.
> -- this would parse fine
> -- because @.sql is a valid variable
> -- and exec(@.sql) syntactically correct
> -- though this would err at runtime
> set parseonly on
> go
> declare @.sql sysname
> set @.sql='aflasfasfaslfsaf'
> exec(@.sql)
> --
> -oj
>
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>
>|||No.
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...[vbcol=seagreen]
> is there any other way that i can make it to work
> srinivas
> "oj" wrote:
>|||Hi
You could exec it prepend with SET PARSEONLY ON?
DECLARE @.sql varchar(8000)
DECLARE @.errval int
SET @.sql = 'SELECT * FROM PUBS..Authors'
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
SET @.sql = 'SELECT * FROM '
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
SET @.sql = 'SEECT * FROM PUBS..Authors'
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
John
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...[vbcol=seagreen]
> is there any other way that i can make it to work
> srinivas
> "oj" wrote:
>|||hi John
what if the table name doesnt exist in the database... say
"select * from authrs" instead of "select * from authors"....
your code still executes it( shouldnt the parse take care of that
too....please correct me if i am wrong)
thanks
red
"John Bell" wrote:

> Hi
> You could exec it prepend with SET PARSEONLY ON?
> DECLARE @.sql varchar(8000)
> DECLARE @.errval int
> SET @.sql = 'SELECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SELECT * FROM '
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SEECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> John
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>
>|||Ah yes. If 'parseonly' is part of the statement. The entire string will get
parsed at runtime (i.e. exec()).
-oj
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23E1YRyrQFHA.2948@.TK2MSFTNGP14.phx.gbl...
> Hi
> You could exec it prepend with SET PARSEONLY ON?
> DECLARE @.sql varchar(8000)
> DECLARE @.errval int
> SET @.sql = 'SELECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SELECT * FROM '
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SEECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> John
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>|||well, parseonly only parses for sql well-formed/syntax. It does not check
for the object's existence.
DECLARE @.sql sysname
SET @.sql = 'SELECT blah '
EXEC ('SET PARSEONLY ON ' + @.SQL)
PRINT(@.@.ERROR)
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:BBD5F63C-9276-49E6-802D-F2A3E93B7791@.microsoft.com...[vbcol=seagreen]
> hi John
> what if the table name doesnt exist in the database... say
> "select * from authrs" instead of "select * from authors"....
> your code still executes it( shouldnt the parse take care of that
> too....please correct me if i am wrong)
> thanks
> red
> "John Bell" wrote:
>|||Hi
Even with dynamic SQL your tables existance should not be in doubt,
otherwise you are almost certainly open to SQL injection
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
Also check out:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
John
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:BBD5F63C-9276-49E6-802D-F2A3E93B7791@.microsoft.com...[vbcol=seagreen]
> hi John
> what if the table name doesnt exist in the database... say
> "select * from authrs" instead of "select * from authors"....
> your code still executes it( shouldnt the parse take care of that
> too....please correct me if i am wrong)
> thanks
> red
> "John Bell" wrote:
>

Parent and grand parent and great grand parent etc.

Hi,
I have a table with id, parent-id.
I want to know how to write a stored procedure which returns the ids of
parent, grand-parent, great-grand-parent etc.
So, if the structure is like this
id Parent-id
Tree Control ASP.NET Controls
ASP.NET Controls ASP.NET
ASP.NET .NET
.NET VS
VS MS
Office MS
A parent can have more then one child
If given 'Tree Control' This stored procedure returns a record set like
following:
Tree Control
ASP.NET Controls
ASP.NET
.NET
VS
MS
ThanksOthers may have a better solution for this, but I worked out the
following for a previous question:
http://groups.google.com/group/SQL-...94663b43a00f67d
Might give you some insight.
HTH,
Stu|||Get a copy of TREES & HIERATRCHIES IN SQL for better ways to do this.|||I am looking for a simple query (at least for you guru guys out there).
For a given node, it should return the list of all the parents of it's
parent/grand parent. Usually people have examples for children - I am
not looking for children, I am looking for parent.
thanks|||Have a look at
http://toponewithties.blogspot.com/...er.htm
l
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Sehboo" <masoodadnan@.hotmail.com> wrote in message
news:1123511813.304233.160130@.o13g2000cwo.googlegroups.com...
>I am looking for a simple query (at least for you guru guys out there).
>
> For a given node, it should return the list of all the parents of it's
> parent/grand parent. Usually people have examples for children - I am
> not looking for children, I am looking for parent.
> thanks
>|||Sorry, anything else? I don't want to get into prime number.
I am just looking for a simple query/stored procedure.
thanks|||OK, What about this one.
http://www.windowsitpro.com/SQLServ...es.blogspot.com
"Sehboo" <masoodadnan@.hotmail.com> wrote in message
news:1123522959.424109.230440@.g49g2000cwa.googlegroups.com...
> Sorry, anything else? I don't want to get into prime number.
> I am just looking for a simple query/stored procedure.
> thanks
>|||Look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
Good design is crucial in these cases - you must never allow for circular
references.
ML

parametrised servername and catalog name

Hi,

I intend to use four-part table names to select data from a Linked Server into local tables. There will be stored procedures containing Insert-Select statements.

While I'm developing, I'm pointing to a development version of the remote server. In production, the remote server will be different. There will be many other situations, where I will need to link to one remote server or another. But I don't want to recompile my stored procs every time.

The question is: can I use variables for the first two parts of a four-part table name. Something like:

declare @.svr varchar(20)
declare @.dsn varchar(20)
select @.svr = 'Pervasive_Test', @.dsn = 'D_drive'
SELECT * from @.svr.@.dsn..remote_table

Would the above work ?

I have to go through the ODBC Provider. The only way my query works is when I also specify the DSN as "catalog" in the second position of the table name. I definitely do not want the name of the DSN to be hardwired into my stored procs.

Andrewyour example wont work, though you can use
sp_executesql (see BOL) to run commands with variable object names,

eg.

set @.statement = N'select user_id from users
where cc_number = '+''''+ltrim(rtrim(@.cc_number))+''''

insert into #master_id (user_id)
exec sp_executesql @.statement

though for the sort of thing you are doing, I usually create a batch file which uses a tool like SED to replace each token (defined earlier on object names) with the desired values , and then use the relevant modified script for each installation...just have the bat file receive the server/tablname as params

Parametric Views

Can I create parameterized view, in other words like stored procedures where
by I can declare a variable and assign to it values and use it in my
criteria?
Thanks,
YahyaYahya
No, you can SELECT <column lists>from a view WHERE col=@.par ( I 'm sure you
knew it)
"Yahya" <x@.x.x> wrote in message
news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures
where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>|||That feature is called "In-line table valued functions" in SQL Server. User defined functions are
documented in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yahya" <x@.x.x> wrote in message news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>

Parametric Views

Can I create parameterized view, in other words like stored procedures where
by I can declare a variable and assign to it values and use it in my
criteria?
Thanks,
Yahya
Yahya
No, you can SELECT <column lists>from a view WHERE col=@.par ( I 'm sure you
knew it)
"Yahya" <x@.x.x> wrote in message
news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures
where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>
|||That feature is called "In-line table valued functions" in SQL Server. User defined functions are
documented in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yahya" <x@.x.x> wrote in message news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>

Parametric Views

Can I create parameterized view, in other words like stored procedures where
by I can declare a variable and assign to it values and use it in my
criteria?
Thanks,
YahyaYahya
No, you can SELECT <column lists>from a view WHERE col=@.par ( I 'm sure you
knew it)
"Yahya" <x@.x.x> wrote in message
news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures
where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>|||That feature is called "In-line table valued functions" in SQL Server. User
defined functions are
documented in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yahya" <x@.x.x> wrote in message news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...een">
> Can I create parameterized view, in other words like stored procedures whe
re
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>

Parameters to SP ?

How can I pass a table variable as a parameter to a stored procedure ?

eg.
create proc procname ( @.param table (col1 int ) )
as
select ....

I get the following error :
Server: Msg 156, Level 15, State 1, Procedure procname, Line 1
Incorrect syntax near the keyword 'table'.

Is there any alternate way to do this ?

Thanks.You cannot pass a table variable as an input to the SP. Not sure about your logic but if it is unavoidable, one way is to populate a temp table and read from the SP and then delete it as soon as you exit from the called SP. This is one option. Sure there could be some better ones.

- CB

Originally posted by Decastod
How can I pass a table variable as a parameter to a stored procedure ?

eg.
create proc procname ( @.param table (col1 int ) )
as
select ....

I get the following error :
Server: Msg 156, Level 15, State 1, Procedure procname, Line 1
Incorrect syntax near the keyword 'table'.

Is there any alternate way to do this ?

Thanks.|||Thanks for your help.
Tired it in another way as you mentioned and it worked.

Parameters to reports that only show is previous parameters filled

I don't know if I can do this with Reporting Services. I have ten parameters to a stored procedure. However, each parameter only shows when the previous one is selected. For sake of this discussion, the queries from which the parameters come are completly unrelated.
Initially, only the pull-down for parameter 1 appears. When the user selects a value for parameter 1 using the initial pull-down, the pull down for parameter 2 should appear. When the user selects a value from this pull down, a pull down from parameter 3 should appear.
At any point, the user can click View Report and pass the parameters he selected and all other parameters null.
Thanks in advance for any help.
JustinYes, RS supports sequenced parameter prompting. You build the valid values
query for a parameter using a query that includes previously selected
parameter values. I believe there is a sample called 'Product Line Sales'
that illustrates this technique.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Justin30519" <Justin30519@.discussions.microsoft.com> wrote in message
news:01FC2CE7-C435-47EC-AD00-E2762510E522@.microsoft.com...
>I don't know if I can do this with Reporting Services. I have ten
>parameters to a stored procedure. However, each parameter only shows when
>the previous one is selected. For sake of this discussion, the queries from
>which the parameters come are completly unrelated.
> Initially, only the pull-down for parameter 1 appears. When the user
> selects a value for parameter 1 using the initial pull-down, the pull down
> for parameter 2 should appear. When the user selects a value from this
> pull down, a pull down from parameter 3 should appear.
> At any point, the user can click View Report and pass the parameters he
> selected and all other parameters null.
> Thanks in advance for any help.
> Justin
>|||> Yes, RS supports sequenced parameter prompting. You build the valid values
> query for a parameter using a query that includes previously selected
> parameter values. I believe there is a sample called 'Product Line Sales'
> that illustrates this technique.
>
Thank you very much for your reply. It was helpful. I can do what my boss asked now. :)
Justin

Parameters Status in Store Procedure

Hi,
Actually we want to know the type of parameters in stored procedure. Means are they Default Parameter or Mandatory. Where this information is store? I can get ISNULLABLE Column from syscolumns table but it is only applicable to table not stored procedu
re.
Please Try To Find it.
Message posted via http://www.sqlmonster.com
You can get parameter name and data type information for stored procedure
parameters from the INFORMATION_SCHEMA.PARAMETERS view. I don't know of a
way to retrieve parameter default values, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"ganesh wayachal via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f81eb353fc9e47b5a8bbd5696b15b3b8@.SQLMonster.c om...
> Hi,
> Actually we want to know the type of parameters in stored procedure.
> Means are they Default Parameter or Mandatory. Where this information is
> store? I can get ISNULLABLE Column from syscolumns table but it is only
> applicable to table not stored procedure.
> Please Try To Find it.
> --
> Message posted via http://www.sqlmonster.com

Parameters Status in Store Procedure

Hi,
Actually we want to know the type of parameters in stored procedure. Means are they Default Parameter or Mandatory. Where this information is store? I can get ISNULLABLE Column from syscolumns table but it is only applicable to table not stored procedure.
Please Try To Find it.
--
Message posted via http://www.sqlmonster.comYou can get parameter name and data type information for stored procedure
parameters from the INFORMATION_SCHEMA.PARAMETERS view. I don't know of a
way to retrieve parameter default values, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ganesh wayachal via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f81eb353fc9e47b5a8bbd5696b15b3b8@.SQLMonster.com...
> Hi,
> Actually we want to know the type of parameters in stored procedure.
> Means are they Default Parameter or Mandatory. Where this information is
> store? I can get ISNULLABLE Column from syscolumns table but it is only
> applicable to table not stored procedure.
> Please Try To Find it.
> --
> Message posted via http://www.sqlmonster.com

Parameters Status in Store Procedure

Hi,
Actually we want to know the type of parameters in stored procedure. Means a
re they Default Parameter or Mandatory. Where this information is store? I c
an get ISNULLABLE Column from syscolumns table but it is only applicable to
table not stored procedu
re.
Please Try To Find it.
Message posted via http://www.droptable.comYou can get parameter name and data type information for stored procedure
parameters from the INFORMATION_SCHEMA.PARAMETERS view. I don't know of a
way to retrieve parameter default values, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"ganesh wayachal via droptable.com" <forum@.droptable.com> wrote in message
news:f81eb353fc9e47b5a8bbd5696b15b3b8@.SQ
droptable.com...
> Hi,
> Actually we want to know the type of parameters in stored procedure.
> Means are they Default Parameter or Mandatory. Where this information is
> store? I can get ISNULLABLE Column from syscolumns table but it is only
> applicable to table not stored procedure.
> Please Try To Find it.
> --
> Message posted via http://www.droptable.com

Parameters passed to Stored Procedure from ASP

I'm relatively new to stored procedure writing. My situation is an ASP page allowing multiple selections from a <select> option passing 1 to x number of options to a stored proc. for the WHERE clause.
For instance, a user selects 1,3,5 and 6. These would need to be passed to the sp and then:

...FROM [tablename]
WHERE (Number = @.param1) OR (Number = @.param2) OR (Number = @.param3) OR (Number = @.param4) etc...

This sp ties into a Crystal Report and in the above scenario, should return data for colums equal to 1 3 5 and 6. If only 1 and 6 had been selected on the ASP page, then only those two would be assigned a value in the sp.

Any suggestions?
Thanks in advance...

IIS 5.0, Win 2k, MSSQL 7.0Just create an ado connection/command objects and execute the stored procedure. What are the ranges for the parameters ?|||Looks like you want to say something like

WHERE col1 IN (@.param1, @.param2, @.param3, @.param4)

Or

WHERE col1 IN (@.param1, @.param2)

whatever the case may be...is that right?

Or are they diferent columns|||Well, if the ASP was passing three different parameters (Start Date, End Date, Details) they would be passed to the sp like(whereas the strStartDate, strEndDate and strDetails were assigned the Request.Form values):

Set ThisParam = StoredProcParamCollection.item(1)
ThisParam.SetCurrentValue cstr(strStartDate), 12

Set ThisParam = StoredProcParamCollection.item(2)
ThisParam.SetCurrentValue cstr(strEndDate), 12

Set ThisParam = StoredProcParamCollection.item(3)
ThisParam.SetCurrentValue cstr(strDetails), 12

However, in my scenario, I need to allow for multiple selections in one Request.Form("select") collection so to speak passed to the sp.

Does that make sense??
:-\|||What is the maximum number of selections in the select box - and will this keep growing ?|||The select box as 12 selections. The user can pick as few as one or as many as all. Basically, any combination. Say they pick 1 & 2. I need to pass those selections to the sp and use those parameters in the where

WHERE [columnname] = @.parameter1 or [columnname] = @.parameter2 or [columnname] = @.parameter3(parameter 3 remains default value since only 1 and two were passed in)

(The parameters are assign the passed values or remain default if no value passed)|||How come you don't ask for a variable result set from the SELECT..can't they pick their own fields too?

Don't want to use the D word....

Have you run a sql statement with all 13 parameters?

Can we see the sproc...

Is it like CREATE PROC mySproc @.Param1 = null, @.Param2 = null

Maybe you can pass all of them

and do WHERE Col1 = ISNULL(@.Param1,Col1) AND...|||You could set up one parameter and pass a delimited string containing all your selections.

You then split the string up and use the in statement to do your selection.

It's not pretty but it will work.

Let me know if you want details of how to do this.|||Brett & rokslide, thanks for the help. Basically, the options aren't added from a db connection into the asp page. So, hard coding the 12 values (which never change as they are campus locations) isn't a problem. So, the intent was to pass any combination of selected campuses, pass them to a sp and generate a report for the campuses selected.

...FROM [tablename]
WHERE (Number = @.param1) OR (Number = @.param2) OR (Number = @.param3) OR (Number = @.param4) etc...

Is basically what I need to do, but I like the delimited idea and splitting the string and doing the select through iteration.

parameters of a given stored procedure

hi,

is it possible to get the list of parameters of a given stored procedure? i need the parameter information like name of the parameter, data type of the parameter, size, type (input or output) etc while the name of the stored procedure will be provided.

with regards,

oh, found it.

select * from information_schema.parameters where specific_name = 'stored procedure name here'

Parameters not working

Hello,
I have created a report based on a stored procedure which uses 13
parameters. The 13 parameter values are mapped to the report parameter
selections (for example: Parameter @.CATEGORY, Value
=Parameters!Category.Value).
When the stored procedure is executed from the 'Data' tab of RS and the
parameters are typed in, the result set is correct.
When the report is executed in 'Preview' tab of RS only the first parameter
is evaluated. The rest of the parameters are not evaluated and the result
set brings everything back regardless of the selections.
Is this a bug or is there a way to enforce report parameter selections to be
passed into the stored procedure?
Thanks in Advance,
JohnParameters are case sensitive. If this is happening to you then something is
wrong with your mapping. I always use the expression builder and select the
report parameter just for this reason.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:3E4BF735-9632-4493-AE77-751DB146D5E9@.microsoft.com...
> Hello,
> I have created a report based on a stored procedure which uses 13
> parameters. The 13 parameter values are mapped to the report parameter
> selections (for example: Parameter @.CATEGORY, Value
> =Parameters!Category.Value).
> When the stored procedure is executed from the 'Data' tab of RS and the
> parameters are typed in, the result set is correct.
> When the report is executed in 'Preview' tab of RS only the first
parameter
> is evaluated. The rest of the parameters are not evaluated and the result
> set brings everything back regardless of the selections.
> Is this a bug or is there a way to enforce report parameter selections to
be
> passed into the stored procedure?
> Thanks in Advance,
> John|||Thanks Bruce,
Actually parameters were selected with expression builder and are correct.
The values for some of these parameters are coming from other stored
procedures. I am wondering if all these values are pulled once prior to
report execution and anything that is changed afterwards is not used.
John K.
===
"Bruce L-C [MVP]" wrote:
> Parameters are case sensitive. If this is happening to you then something is
> wrong with your mapping. I always use the expression builder and select the
> report parameter just for this reason.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John K" <JohnK@.discussions.microsoft.com> wrote in message
> news:3E4BF735-9632-4493-AE77-751DB146D5E9@.microsoft.com...
> > Hello,
> >
> > I have created a report based on a stored procedure which uses 13
> > parameters. The 13 parameter values are mapped to the report parameter
> > selections (for example: Parameter @.CATEGORY, Value
> > =Parameters!Category.Value).
> >
> > When the stored procedure is executed from the 'Data' tab of RS and the
> > parameters are typed in, the result set is correct.
> >
> > When the report is executed in 'Preview' tab of RS only the first
> parameter
> > is evaluated. The rest of the parameters are not evaluated and the result
> > set brings everything back regardless of the selections.
> >
> > Is this a bug or is there a way to enforce report parameter selections to
> be
> > passed into the stored procedure?
> >
> > Thanks in Advance,
> > John
>
>|||The parameters that you see in the toolbar prior to clicking on view report
are what should be getting sent to your stored procedure. One thing to keep
in mind, if the parameters are not changed then the data is cached (in the
development environment) and then next time you preview it uses the cached
data (look where you have your rdl files and you will see
reportname.rdl.data, that is what the .data files are.
I am not really sure what you are seeing but it could be a side affect of
that.
If the parameters are supposed to be done in a certain execution order then
you need to make them cascading parameters. Search books on line for
cascading parameter
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:35EA39B3-D88E-4EE4-973B-D162686C48EA@.microsoft.com...
> Thanks Bruce,
> Actually parameters were selected with expression builder and are correct.
> The values for some of these parameters are coming from other stored
> procedures. I am wondering if all these values are pulled once prior to
> report execution and anything that is changed afterwards is not used.
> John K.
> ===> "Bruce L-C [MVP]" wrote:
> > Parameters are case sensitive. If this is happening to you then
something is
> > wrong with your mapping. I always use the expression builder and select
the
> > report parameter just for this reason.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "John K" <JohnK@.discussions.microsoft.com> wrote in message
> > news:3E4BF735-9632-4493-AE77-751DB146D5E9@.microsoft.com...
> > > Hello,
> > >
> > > I have created a report based on a stored procedure which uses 13
> > > parameters. The 13 parameter values are mapped to the report
parameter
> > > selections (for example: Parameter @.CATEGORY, Value
> > > =Parameters!Category.Value).
> > >
> > > When the stored procedure is executed from the 'Data' tab of RS and
the
> > > parameters are typed in, the result set is correct.
> > >
> > > When the report is executed in 'Preview' tab of RS only the first
> > parameter
> > > is evaluated. The rest of the parameters are not evaluated and the
result
> > > set brings everything back regardless of the selections.
> > >
> > > Is this a bug or is there a way to enforce report parameter selections
to
> > be
> > > passed into the stored procedure?
> > >
> > > Thanks in Advance,
> > > John
> >
> >
> >|||Yes, some of the parameters are cascading.
I tried to change the type from Stored Procedure to Text and typed the
parameters out instead of using the paramter tab (like so: exec usp_x
@.Status=1, @.Project='HELLO') which works fine.
I just need the hardcoded 'HELLO' to be the value selected on the report
parameter which has the same name as the expected stored procedure parameter
(@.Project).
I tried exec usp_x @.Status=1, @.Project=Parameters!Project.Value!
which returns incorrect syntax near '!'
Thanks again.
"Bruce L-C [MVP]" wrote:
> The parameters that you see in the toolbar prior to clicking on view report
> are what should be getting sent to your stored procedure. One thing to keep
> in mind, if the parameters are not changed then the data is cached (in the
> development environment) and then next time you preview it uses the cached
> data (look where you have your rdl files and you will see
> reportname.rdl.data, that is what the .data files are.
> I am not really sure what you are seeing but it could be a side affect of
> that.
> If the parameters are supposed to be done in a certain execution order then
> you need to make them cascading parameters. Search books on line for
> cascading parameter
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John K" <JohnK@.discussions.microsoft.com> wrote in message
> news:35EA39B3-D88E-4EE4-973B-D162686C48EA@.microsoft.com...
> > Thanks Bruce,
> >
> > Actually parameters were selected with expression builder and are correct.
> > The values for some of these parameters are coming from other stored
> > procedures. I am wondering if all these values are pulled once prior to
> > report execution and anything that is changed afterwards is not used.
> >
> > John K.
> >
> > ===> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Parameters are case sensitive. If this is happening to you then
> something is
> > > wrong with your mapping. I always use the expression builder and select
> the
> > > report parameter just for this reason.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "John K" <JohnK@.discussions.microsoft.com> wrote in message
> > > news:3E4BF735-9632-4493-AE77-751DB146D5E9@.microsoft.com...
> > > > Hello,
> > > >
> > > > I have created a report based on a stored procedure which uses 13
> > > > parameters. The 13 parameter values are mapped to the report
> parameter
> > > > selections (for example: Parameter @.CATEGORY, Value
> > > > =Parameters!Category.Value).
> > > >
> > > > When the stored procedure is executed from the 'Data' tab of RS and
> the
> > > > parameters are typed in, the result set is correct.
> > > >
> > > > When the report is executed in 'Preview' tab of RS only the first
> > > parameter
> > > > is evaluated. The rest of the parameters are not evaluated and the
> result
> > > > set brings everything back regardless of the selections.
> > > >
> > > > Is this a bug or is there a way to enforce report parameter selections
> to
> > > be
> > > > passed into the stored procedure?
> > > >
> > > > Thanks in Advance,
> > > > John
> > >
> > >
> > >
>
>|||Corrected the syntax to:
exec usp_x @.Status=1, @.Project=[Parameters!Project.Value!]
but it still not used in the reporting window.
Also tried: exec usp_X @.Status, @.Project to reference the parameters
directly. Still having problems.
If there are any examples out there please point me that direction.
Thanks.|||When you do a stored procedure do the following steps. I tend to use command
type as text but that is because I tend to go against Sybase a lot (I am
currently creating a datamart and will be doing most of my reporting against
SQL Server). First thing at work tomorrow I'll give you a stored procedure
example with SQL Server. The format
exec usp_x @.Status=1, @.Project=[Parameters!Project.Value!]
will not work. I am surprised it didn't error out for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:9A94F3C6-54BE-487D-9C43-359F806FE7B9@.microsoft.com...
> Corrected the syntax to:
> exec usp_x @.Status=1, @.Project=[Parameters!Project.Value!]
> but it still not used in the reporting window.
> Also tried: exec usp_X @.Status, @.Project to reference the parameters
> directly. Still having problems.
> If there are any examples out there please point me that direction.
> Thanks.|||Bruce thanks again,
Unfortunately the attachment did not make it, can you please e-mail it to
yiannino@.hotmail.com?
John K.|||In Outlook express the attachment shows as a paperclip. I know some people
use web based readers and seem to not see attachments. I prefer to do all my
responses here but I will email it to you. If you have additional questions
about the report please just respond here so others can benefit as well.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:60B099C4-5A3F-41FD-B130-24226143E8C5@.microsoft.com...
> Bruce thanks again,
> Unfortunately the attachment did not make it, can you please e-mail it to
> yiannino@.hotmail.com?
> John K.|||Hi Bruce,
Sorry it took a while. I was able to recreate the issue and it seems that
the problem lies with the way stored procedures are evaluated by Reporting
Services in terms of what parameters are used in the preview.
Specifically, the original stored procedure declared a cursor (which
required a select from another table) and then called another stored
procedure within the cursor. Seems that Reporting Services evalutated this
first select statement to decide what parameters would be applicable for the
report.
I changed the stored procedure to have as the first statement the select
(dynamically build) with all the parameters that are being passed in, and
that seems to work.
So is this some type of RS limitation where parameters to be evaluated at
execution time are defined by the first sql statement found/returned?
Is there anything coming in SQL Server 2005 that would allow a Cursor based
on another table to be used?
Any other ideas that would allow me to use the stored procedures as they
exist today instead of having to rewrite them?
Thanks again for all your help,
John