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:
>

Parse output from xp_cmdshell

I would like to write the output from (Exec master..xp_cmdshell 'dir
E:\NewOrleans\*FULL.BAK') to a variable. I would like to test each line of
the output variable to see if (wildcard.BAK) exist. If (wildcard.BAK) exis
t
I append this code to my existing script.
Please help me create this script.
Thanks,
-- directory full backup listing
Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK'
Output from (Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK')
Volume in drive E is NWORLBD01A Dumps
Volume Serial Number is DRV-73B6
NULL
Directory of E:\NewOrleans
NULL
05/12/2005 12:57 AM 168,988,440,064 STARDEV_db_200505152100.BAK
1 File(s) 168,968,064 bytes
0 Dir(s) 295,359,264 bytes free
NULLJoe K. wrote:
> I would like to write the output from (Exec master..xp_cmdshell 'dir
> E:\NewOrleans\*FULL.BAK') to a variable. I would like to test each
> line of the output variable to see if (wildcard.BAK) exist. If
> (wildcard.BAK) exist I append this code to my existing script.
> Please help me create this script.
> Thanks,
> -- directory full backup listing
> Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK'
>
> Output from (Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK')
> Volume in drive E is NWORLBD01A Dumps
> Volume Serial Number is DRV-73B6
> NULL
> Directory of E:\NewOrleans
> NULL
> 05/12/2005 12:57 AM 168,988,440,064 STARDEV_db_200505152100.BAK
> 1 File(s) 168,968,064 bytes
> 0 Dir(s) 295,359,264 bytes free
> NULL
Create Table #output (output varchar(1000))
insert into #output
Exec master..xp_cmdshell 'dir C:'
Select * from #output
drop table #output
David Gugick
Imceda Software
www.imceda.com|||Look at this example:
create table #ipconfig(line varchar(2000))
insert into #ipconfig
execute xp_cmdshell 'ipconfig.exe'
select line from #ipconfig
drop table #ipconfig
YOu can "parse" that table with
Select * form #ipconfig where line like '%Something%'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Joe K." <Joe K.@.discussions.microsoft.com> schrieb im Newsbeitrag
news:4D55228E-D4E2-4EA0-A533-1B4556C5232F@.microsoft.com...
> I would like to write the output from (Exec master..xp_cmdshell 'dir
> E:\NewOrleans\*FULL.BAK') to a variable. I would like to test each line
> of
> the output variable to see if (wildcard.BAK) exist. If (wildcard.BAK)
> exist
> I append this code to my existing script.
> Please help me create this script.
> Thanks,
> -- directory full backup listing
> Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK'
>
> Output from (Exec master..xp_cmdshell 'dir E:\NewOrleans\*FULL.BAK')
> Volume in drive E is NWORLBD01A Dumps
> Volume Serial Number is DRV-73B6
> NULL
> Directory of E:\NewOrleans
> NULL
> 05/12/2005 12:57 AM 168,988,440,064 STARDEV_db_200505152100.BAK
> 1 File(s) 168,968,064 bytes
> 0 Dir(s) 295,359,264 bytes free
> NULL
>|||Create temp table and change my path to you path and it will give you
directory returned data in a table then you extract the peaces you need.
Create TABLE #temp
(
data nvarchar (1000)
)
Insert #temp
Exec master..xp_cmdshell 'dir \\server\backup\sql\FULL_Backup\*(FULL)*
.BKP'
select * from #temp
"Jens Sü?meyer" wrote:

> Look at this example:
> create table #ipconfig(line varchar(2000))
> insert into #ipconfig
> execute xp_cmdshell 'ipconfig.exe'
> select line from #ipconfig
> drop table #ipconfig
>
> YOu can "parse" that table with
> Select * form #ipconfig where line like '%Something%'
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Joe K." <Joe K.@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:4D55228E-D4E2-4EA0-A533-1B4556C5232F@.microsoft.com...
>
>

Parse IP address

I am fairly new to SQL and am trying to write a function to parse the ip address into 4 sections. I have been searching through the forums to see if anyone has a posted example of parsing an ip address but could not find one.

I am wondering what would be the best method of doing this, or if anyone has an example.

Thank youlook at SUBSTRING and CHARINDEX in books online and think about how you could nest them.|||I found a better answer, builtin function PARSENAME

takes the 4 sections between the . and seperates them. only 1 line of code for each section:

,PARSENAME(ip, 4) AS 'Sec1'
,PARSENAME(ip, 3) AS 'Sec2'
,PARSENAME(ip, 2) AS 'Sec3'
,PARSENAME(ip, 1) AS 'Sec4'

Thanks|||Holy non-standard usage Batman! :shocked:

Just goes to show what a motivated user will do (and drive the developers crazy in the process).

Kudos to you for the unorthodox approach. Hope it works.

Regards,

hmscott|||I found a better answer, builtin function PARSENAME

takes the 4 sections between the . and seperates them. only 1 line of code for each section:


,PARSENAME(ip, 4) AS 'Sec1'
,PARSENAME(ip, 3) AS 'Sec2'
,PARSENAME(ip, 2) AS 'Sec3'
,PARSENAME(ip, 1) AS 'Sec4'

Lol - that is brilliant.
There have been a few discussions on how to store IP addresses (char, 4 tinyints, integer etc). This is a new way on me to split the human-friendly form though!|||yup it works great! Def the easiest way to do it :)|||if you have a string with more parts, try this one:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

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

parametized inserts in a loop

Hi,

I have a for next loop that I am using to write some data into a table.

The problem I have is one of the fields is an @.parameter

The relevant bit of VB.net code is:

Dim dbpm_theDateTimeAs System.Data.IDataParameter =New System.Data.SqlClient.SqlParameter

dbConnection.Open()

For intCounterAsInteger = 0To ds.Tables(0).Rows.Count - 1

querystring ="Insert into tblActionsToDo ([TheDT], [username]) VALUES (@.theDateTime, '" & username &"')"

dbpm_theDateTime =New System.Data.SqlClient.SqlParameter

dbpm_theDateTime.ParameterName ="@.theDateTime"

dbpm_theDateTime.Value = tmpstrDT

dbpm_theDateTime.DbType = System.Data.DbType.DateTime

dbCommand.Parameters.Add(dbpm_theDateTime)

dbCommand.CommandText = querystring

rowsAffected = dbCommand.ExecuteNonQuery

Next

The first time through the for next loop, everything works fine. The next time though I get an error that @.theDateTime has already been defined. To be fair, it is true; but how do I get round this? I've tried making the @.parameter a string so that I can manipulate it as "@.parameter" & intcounter, and thus have a uniquely-named varaible for every loop, but it doesn't work.

thanks for any pointers

I fixed this one out on my own.

All I had to do was move everything outside of the loop except for

dbpm_theDateTime.Value = tmpstrDT

and it works just fine :)

Parameters in an expression

I am trying to write an expression based on a parameter for a text box.
If I write it without the parameter it works fine, but when I add the
parameter, the expression evaluates as just text.
My dataset contains a field called "PeakSessions"
I have a parameter called "chrt", type string, containing the value
"PeakSessions".
=Fields!PeakSessions.Value <= Works fine. Displays the PeakSession
data.
="Fields!" & Parameters!chrt.Value & ".Value"
This parameterized version evaluates to: Fields!PeakSessions.Value.
So it seems the parameter resolved correctly, but my expression is
being evaluated as text. What am I doing wrong?
SQL 2005 Reports Services, SP1, Standard EditionYou are just creating a string. Let's say you were trying to create a string
to display. This is how you would do it. RS will not evaluate the string
which is what you are trying to do. You should be able to do something like
this:
=Fields(Parameters!chrt.Value).Value
Just a guess.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"randscott" <rscott@.plato.com> wrote in message
news:1152724801.223473.94690@.h48g2000cwc.googlegroups.com...
>I am trying to write an expression based on a parameter for a text box.
> If I write it without the parameter it works fine, but when I add the
> parameter, the expression evaluates as just text.
> My dataset contains a field called "PeakSessions"
> I have a parameter called "chrt", type string, containing the value
> "PeakSessions".
> =Fields!PeakSessions.Value <= Works fine. Displays the PeakSession
> data.
> ="Fields!" & Parameters!chrt.Value & ".Value"
> This parameterized version evaluates to: Fields!PeakSessions.Value.
> So it seems the parameter resolved correctly, but my expression is
> being evaluated as text. What am I doing wrong?
> SQL 2005 Reports Services, SP1, Standard Edition
>|||You are right. I was just creating text, but I couldn't figure out the
right way. I had tried something similar to your suggestion
=Fields!(Parameters!chrt.Value).Value but the extra exclamation point
caused me problems. Your way worked. Thanks!
Bruce L-C [MVP] wrote:
> You are just creating a string. Let's say you were trying to create a string
> to display. This is how you would do it. RS will not evaluate the string
> which is what you are trying to do. You should be able to do something like
> this:
> =Fields(Parameters!chrt.Value).Value|||Good. Glad it worked.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"randscott" <rscott@.plato.com> wrote in message
news:1152729191.900859.300580@.m79g2000cwm.googlegroups.com...
> You are right. I was just creating text, but I couldn't figure out the
> right way. I had tried something similar to your suggestion
> =Fields!(Parameters!chrt.Value).Value but the extra exclamation point
> caused me problems. Your way worked. Thanks!
>
> Bruce L-C [MVP] wrote:
>> You are just creating a string. Let's say you were trying to create a
>> string
>> to display. This is how you would do it. RS will not evaluate the string
>> which is what you are trying to do. You should be able to do something
>> like
>> this:
>> =Fields(Parameters!chrt.Value).Value
>

Parameterizing the XPath for a modify()

Hi all,

I'm trying to write a generic stored procedure which will parameterize the XPath in my XML file so that I can update a value by giving just its path and the new value I wish to store:

Here is the code:
CREATE PROCEDURE ModifyLoanXML
@.LoanNumber char(60),
@.XPathQuery varchar(300),
@.Value varchar(300)
AS
UPDATE Loans SET LoanXML.modify('replace value of (sql:variable("@.XPathQuery"))[1] with xs:string(sql:variable("@.Value"))')
WHERE Loans.LoanNumber = @.LoanNumber
GO

Unfortunately I am getting the following error:
Msg 2337, Level 16, State 1, Procedure ModifyLoanXML, Line 6
XQuery [Loans.LoanXML.modify()]: The target of 'replace' must be at most one node, found 'xs:string ?'

I've tried a number of things to try to get this to go through but I'm having no luck. Is it possible to completely parameterize the XPath you wish to change when calling XQuery.modify() ?

Is there any way to specify that my path will always point to an attribute (not a node) so that this proc can be created? Thanks!

-Karthik Hariharan

The path specified in modify() must be a string literal, so the approach you are taking will not work out. The sql:variable("@.XPathQuery") is not interpreted as a path, but as a string value.

You can use dynamic sql to achieve what you are looking for. SInce you will be generating dynamic SQL, you will need to take steps to ensure that you dont end up with SQL injection from accepting untrusted XPaths.

|||Thanks Todd. I was trying to avoid the dynamic SQL method precisely to avoid any SQL injection vulnerabilities. Is there no way to achieve this dynamix XQuery using a stored procedure? If anyone else has a suggestion please let me know. Thanks.
|||I resolved the issue by using an Exec() T-SQL command. Here is my code below:

CREATE PROCEDURE [dbo].[ModifyLoanXML]
@.LoanID UNIQUEIDENTIFIER,
@.XPathQuery varchar(max),
@.Value varchar(max)
AS
DECLARE @.query varchar(max)
DECLARE @.LoanIDstr varchar(max)
SET @.LoanIDStr = CONVERT (varchar(max),@.LoanID)

SET @.query='UPDATE Loans SET LoanXML.modify(''declare namespace MISMO="http://mrgdev.local/mismo/";replace value of ' + @.XPathQuery + ' with "' +@.Value +'"'') WHERE Loans.InternalID = ''' + @.LoanIDstr + ''''
exec(@.query)

To avoid a possible SQL injection, I parameterized the LoanID and convert it to a varchar within the stored procedure. Just wanted to share this with you all.

Regards,
Karthik Hariharan
|||

Hi Karthik,

Your code still appears to be subject to SQL injection attacks with respect to the XPathQuery variable and the Value variable, if either is untrusted. Since you are concatenating them with the SQL string, if an untrusted user was able to specify the XPathQuery or Value variable, then they may be able to embed quotes and comment characters to change the behavior of your query. You can change your query to parameterize both Value (using sql:variable) and LoadIDstr (using a parameter) but accepting untrusted XPathQuery will be difficult without fully validating that it is safe.

Parameterized View or Parameterized Stored Procedure

Dear All,
i have a complicated query which needs a parameter. I find it too cumbersone
to write the query in vb - instead can i create a stored procedure and pass
the parameter from vb and the stored procedure returns a recorset fully
loaded.
is it possible to create a parameterised query like in Access. My example
works fine in ms access. Only now i am whether to use a stored
procedure or a view?
i am just starting and am using sql server 7.0 and vb. PLS Help Me.
Thanks
Manish
Three Cheers to technet for the Help!Yes you most certainly can... In fact, (although it's not always adviseable)
,
you can do almost anything in a stored proc that you could do in code.
(although some things are a bit harder).
You can definitely pass parameters to a stored Proc.. They can be the
equivilent of byval, or if you want to be be to access, on the client, the
value as modified by the SP, you can pass them "by reference". SQL Code in
the Stored Proc can use the passed in parameters to control the execution of
the SQL. There are many examples in Books On Line (BOL). Available to you
if you have installed SQL Client tools om your development box.
"Manish Sawjiani" wrote:

> Dear All,
> i have a complicated query which needs a parameter. I find it too cumberso
ne
> to write the query in vb - instead can i create a stored procedure and pas
s
> the parameter from vb and the stored procedure returns a recorset fully
> loaded.
> is it possible to create a parameterised query like in Access. My example
> works fine in ms access. Only now i am whether to use a stored
> procedure or a view?
> i am just starting and am using sql server 7.0 and vb. PLS Help Me.
> Thanks
> Manish
>
> --
> Three Cheers to technet for the Help!|||Thanks CBretana for your encouraging response and i will look up the books
online for help. Thanks again. What about a view? is view like a SP?
"CBretana" wrote:
> Yes you most certainly can... In fact, (although it's not always adviseabl
e),
> you can do almost anything in a stored proc that you could do in code.
> (although some things are a bit harder).
> You can definitely pass parameters to a stored Proc.. They can be the
> equivilent of byval, or if you want to be be to access, on the client, the
> value as modified by the SP, you can pass them "by reference". SQL Code
in
> the Stored Proc can use the passed in parameters to control the execution
of
> the SQL. There are many examples in Books On Line (BOL). Available to yo
u
> if you have installed SQL Client tools om your development box.
> "Manish Sawjiani" wrote:
>|||Adding to Charles' suggestion, views and stored procedures are intended for
different purposes, though in few cases, one could use a view instead of a
stored procedure and vice-versa. The primary purpose of a view is logical
data independence and data security.
There are no parameterized views in SQL Server. The general alternative
which is often suggested is a table valued UDF ( user-defined function )
which can be used inline. However UDFs are introduced only in SQL Server
2000 so you will have to use stored procedures instead, perhaps with
re-written constructs.
Anith

Parameterized queries with RDA

Hello..

Is there a way to use parameterized queries with RDA method? I write a program for WinCE5.0 and when I submit a query I use hardcoded date format and this causes problems in different systems.There's a solution for this?

Thanks in advance.

Best option is to use a stored procedure on the server and use RDA to execute it.

Darren

Parameterized Expression

I am trying to write an expression based on a parameter for a text box.
If I write it without the parameter it works fine, but when I add the
parameter, the expression evaluates as just text.

My dataset contains a field called "PeakSessions"
I have a parameter called "chrt", type string, containing the value
"PeakSessions".

=Fields!PeakSessions.Value <= Works fine. Displays the PeakSession data.

="Fields!" & Parameters!chrt.Value & ".Value"

This parameterized version evaluates to: Fields!PeakSessions.Value.
So it seems the parameter resolved correctly, but my expression is
being evaluated as text. What am I doing wrong?

SQL 2005 Reports Services, SP1, Standard Edition

I don′t think taht this will work, perhaps you might be able to get the data through the object model in custom code. I never tried that for myself, so I ccan not help you with that.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

A user in another forum gave me the correct syntax. I had tried every combination similar to this, but never got it right.

=Fields(Parameters!chrt.Value).Value

|||Cool, yes this is what I ment by object model, but I wasn′t aware that it was that wasy :-D|||

Cool, yes this is what I ment by object model, but I wasn′t aware that it was that easy :-D