To search for partial string we are using WHERE column_name LIKE '%str%'.
But its taking long time to return the results. Is there any way to improve
the performance.
What is the datatype of the column you are searching against. An index would
help majorly but it has to been an allowable type.
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:2C4BB57B-48AA-41A1-9594-48DCA08AB50A@.microsoft.com...
> To search for partial string we are using WHERE column_name LIKE
> '%str%'.
> But its taking long time to return the results. Is there any way to
> improve
> the performance.
|||datatype of the column is varchar(max). This column contains the description
of the product. Users want to search for a string rather than a word. If
search by a word is the requirement, I would have gone with FTE.
"Warren Brunk" wrote:
> What is the datatype of the column you are searching against. An index would
> help majorly but it has to been an allowable type.
> --
> /*
> Warren Brunk - MCITP,MCTS,MCDBA
> www.techintsolutions.com
> */
> "Ramu" <Ramu@.discussions.microsoft.com> wrote in message
> news:2C4BB57B-48AA-41A1-9594-48DCA08AB50A@.microsoft.com...
>
>
|||using a wildcard at the start of a LIKE expression does not use an INDEX SEEK
but rather an INDEX SCAN so the query will be slow even if you created
indexes on this column
http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx
Misbah Arefin
"Ramu" wrote:
[vbcol=seagreen]
> datatype of the column is varchar(max). This column contains the description
> of the product. Users want to search for a string rather than a word. If
> search by a word is the requirement, I would have gone with FTE.
>
> "Warren Brunk" wrote:
Showing posts with label string. Show all posts
Showing posts with label string. Show all posts
Friday, March 30, 2012
Part of string
Need help..
I need to select from a text field (lastname, firstname) the first part which is the last name. The format is exactly like the parenthesis. Any ideas?
Thanxtry this
select substring('last,first',1,charindex('first,last'))
i hav'nt checked it.. I think u may have to alter it a little|||Assuming there is always a comma
declare @.name char(30)
set @.name = 'GATES, BILL'
select left(@.name,charindex(',',@.name)-1)
returns
GATES|||Isn't this your lucky day? I just adapted and updated this function yesterday off of some old Access Basic code I had.
Call it like this: "Select dbo.FormatName([YourNameString], 'L')" and it should give you what you want. It handles names in multiple formats. If you find a name string it won't handle, let me know so I can update it.
create function FormatName(@.NameString varchar(100), @.NameFormat varchar(20))
returns varchar(100) as
begin
--blindman, 11/04
--FormatName decodes a NameString into its component parts and returns it in a requested format.
--@.NameString is the raw value to be parsed.
--@.NameFormat is a string that defines the output format. Each letter in the string represents
--a component of the name in the order that it is to be returned.
-- [H] = Full honorific
-- [h] = Abbreviated honorific
-- [F] = First name
-- [f] = First initial
-- [M] = Middle name
-- [m] = Middle initial
-- [L] = Last name
-- [l] = Last initial
-- [S] = Full suffix
-- [s] = Abbreviated suffix
-- [.] = Period
-- [,] = Comma
-- [ ] = Space
--Test variables
-- declare @.NameString varchar(50)
-- declare @.NameFormat varchar(20)
-- set @.NameFormat = 'L, h. F m. s.'
-- set @.NameString = 'Father Gregory Robert Von Finzer Jr'
Declare @.Honorific varchar(20)
Declare @.FirstName varchar(20)
Declare @.MiddleName varchar(30)
Declare @.LastName varchar(30)
Declare @.Suffix varchar(20)
Declare @.TempString varchar(100)
Declare @.IgnorePeriod char(1)
--Prepare the string
--Make sure each period is followed by a space character.
set @.NameString = rtrim(ltrim(replace(@.NameString, '.', '. ')))
--Eliminate double-spaces.
while charindex(' ', @.NameString) > 0 set @.NameString = replace(@.NameString, ' ', ' ')
--Eliminate periods
while charindex('.', @.NameString) > 0 set @.NameString = replace(@.NameString, '.', '')
--If the lastname is listed first, strip it off.
set @.TempString = rtrim(left(@.NameString, charindex(' ', @.NameString)))
if @.TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE') set @.TempString = rtrim(left(@.NameString, charindex(' ', @.NameString, len(@.TempString)+2)))
if right(@.TempString, 1) = ',' set @.LastName = left(@.TempString, len(@.TempString)-1)
if len(@.LastName) > 0 set @.NameString = ltrim(right(@.NameString, len(@.NameString) - len(@.TempString)))
--Get rid of any remaining commas
while charindex(',', @.NameString) > 0 set @.NameString = replace(@.NameString, ',', '')
--Get Honorific and strip it out of the string
set @.TempString = rtrim(left(@.NameString, charindex(' ', @.NameString + ' ')))
if @.TempString in ('MR', 'MRS', 'MS', 'DR', 'Doctor', 'REV', 'Reverend', 'SIR', 'HON', 'Honorable', 'MAJ', 'Major', 'PVT', 'Private', 'FATHER', 'SISTER') set @.Honorific = @.TempString
if len(@.Honorific) > 0 set @.NameString = ltrim(right(@.NameString, len(@.NameString) - len(@.TempString)))
--Get Suffix and strip it out of the string
set @.TempString = ltrim(right(@.NameString, charindex(' ', Reverse(@.NameString) + ' ')))
if @.TempString in ('Jr', 'Sr', 'II', 'III', 'Esq', 'Junior', 'Senior') set @.Suffix = @.TempString
if len(@.Suffix) > 0 set @.NameString = rtrim(left(@.NameString, len(@.NameString) - len(@.TempString)))
if @.LastName is null
begin
--Get LastName and strip it out of the string
set @.LastName = ltrim(right(@.NameString, charindex(' ', Reverse(@.NameString) + ' ')))
set @.NameString = rtrim(left(@.NameString, len(@.NameString) - len(@.LastName)))
--Check to see if the last name has two parts
set @.TempString = ltrim(right(@.NameString, charindex(' ', Reverse(@.NameString) + ' ')))
if @.TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE')
begin
set @.LastName = @.TempString + ' ' + @.LastName
set @.NameString = rtrim(left(@.NameString, len(@.NameString) - len(@.TempString)))
end
end
--Get FirstName and strip it out of the string
set @.FirstName = rtrim(left(@.NameString, charindex(' ', @.NameString + ' ')))
set @.NameString = ltrim(right(@.NameString, len(@.NameString) - len(@.FirstName)))
--Anything remaining is MiddleName
set @.MiddleName = @.NameString
--Create the output string
set @.TempString = ''
while len(@.NameFormat) > 0
begin
if @.IgnorePeriod = 'F' or left(@.NameFormat, 1) <> '.'
begin
set @.IgnorePeriod = 'F'
set @.TempString = @.TempString +
case ascii(left(@.NameFormat, 1))
when '72' then case @.Honorific
when 'Dr' then 'Doctor'
when 'Rev' then 'Reverend'
when 'Hon' then 'Honorable'
when 'Maj' then 'Major'
when 'Pvt' then 'Private'
else isnull(@.Honorific, '')
end
when '70' then isnull(@.FirstName, '')
when '77' then isnull(@.MiddleName, '')
when '76' then isnull(@.LastName, '')
when '83' then case @.Suffix
when 'Jr' then 'Junior'
when 'Sr' then 'Senior'
when 'Esq' then 'Esquire'
else isnull(@.Suffix, '')
end
when '104' then case @.Honorific
when 'Doctor' then 'Dr'
when 'Reverend' then 'Rev'
when 'Honorable' then 'Hon'
when 'Major' then 'Maj'
when 'Private' then 'Pvt'
else isnull(@.Honorific, '')
end
when '102' then isnull(left(@.FirstName, 1), '')
when '109' then isnull(left(@.MiddleName, 1), '')
when '108' then isnull(left(@.LastName, 1), '')
when '115' then case @.Suffix
when 'Junior' then 'Jr'
when 'Senior' then 'Sr'
when 'Esquire' then 'Esq'
else isnull(@.Suffix, '')
end
when '46' then case right(@.TempString, 1)
when ' ' then ''
else '.'
end
when '44' then case right(@.TempString, 1)
when ' ' then ''
else ','
end
when '32' then case right(@.TempString, 1)
when ' ' then ''
else ' '
end
else ''
end
if ((ascii(left(@.NameFormat, 1)) = 72 and @.Honorific in ('FATHER', 'SISTER'))
or (ascii(left(@.NameFormat, 1)) = 115 and @.Suffix in ('II', 'III')))
set @.IgnorePeriod = 'T'
end
set @.NameFormat = right(@.NameFormat, len(@.NameFormat) - 1)
end
-- select ltrim(rtrim(@.TempString))
Return @.TempString
end
I need to select from a text field (lastname, firstname) the first part which is the last name. The format is exactly like the parenthesis. Any ideas?
Thanxtry this
select substring('last,first',1,charindex('first,last'))
i hav'nt checked it.. I think u may have to alter it a little|||Assuming there is always a comma
declare @.name char(30)
set @.name = 'GATES, BILL'
select left(@.name,charindex(',',@.name)-1)
returns
GATES|||Isn't this your lucky day? I just adapted and updated this function yesterday off of some old Access Basic code I had.
Call it like this: "Select dbo.FormatName([YourNameString], 'L')" and it should give you what you want. It handles names in multiple formats. If you find a name string it won't handle, let me know so I can update it.
create function FormatName(@.NameString varchar(100), @.NameFormat varchar(20))
returns varchar(100) as
begin
--blindman, 11/04
--FormatName decodes a NameString into its component parts and returns it in a requested format.
--@.NameString is the raw value to be parsed.
--@.NameFormat is a string that defines the output format. Each letter in the string represents
--a component of the name in the order that it is to be returned.
-- [H] = Full honorific
-- [h] = Abbreviated honorific
-- [F] = First name
-- [f] = First initial
-- [M] = Middle name
-- [m] = Middle initial
-- [L] = Last name
-- [l] = Last initial
-- [S] = Full suffix
-- [s] = Abbreviated suffix
-- [.] = Period
-- [,] = Comma
-- [ ] = Space
--Test variables
-- declare @.NameString varchar(50)
-- declare @.NameFormat varchar(20)
-- set @.NameFormat = 'L, h. F m. s.'
-- set @.NameString = 'Father Gregory Robert Von Finzer Jr'
Declare @.Honorific varchar(20)
Declare @.FirstName varchar(20)
Declare @.MiddleName varchar(30)
Declare @.LastName varchar(30)
Declare @.Suffix varchar(20)
Declare @.TempString varchar(100)
Declare @.IgnorePeriod char(1)
--Prepare the string
--Make sure each period is followed by a space character.
set @.NameString = rtrim(ltrim(replace(@.NameString, '.', '. ')))
--Eliminate double-spaces.
while charindex(' ', @.NameString) > 0 set @.NameString = replace(@.NameString, ' ', ' ')
--Eliminate periods
while charindex('.', @.NameString) > 0 set @.NameString = replace(@.NameString, '.', '')
--If the lastname is listed first, strip it off.
set @.TempString = rtrim(left(@.NameString, charindex(' ', @.NameString)))
if @.TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE') set @.TempString = rtrim(left(@.NameString, charindex(' ', @.NameString, len(@.TempString)+2)))
if right(@.TempString, 1) = ',' set @.LastName = left(@.TempString, len(@.TempString)-1)
if len(@.LastName) > 0 set @.NameString = ltrim(right(@.NameString, len(@.NameString) - len(@.TempString)))
--Get rid of any remaining commas
while charindex(',', @.NameString) > 0 set @.NameString = replace(@.NameString, ',', '')
--Get Honorific and strip it out of the string
set @.TempString = rtrim(left(@.NameString, charindex(' ', @.NameString + ' ')))
if @.TempString in ('MR', 'MRS', 'MS', 'DR', 'Doctor', 'REV', 'Reverend', 'SIR', 'HON', 'Honorable', 'MAJ', 'Major', 'PVT', 'Private', 'FATHER', 'SISTER') set @.Honorific = @.TempString
if len(@.Honorific) > 0 set @.NameString = ltrim(right(@.NameString, len(@.NameString) - len(@.TempString)))
--Get Suffix and strip it out of the string
set @.TempString = ltrim(right(@.NameString, charindex(' ', Reverse(@.NameString) + ' ')))
if @.TempString in ('Jr', 'Sr', 'II', 'III', 'Esq', 'Junior', 'Senior') set @.Suffix = @.TempString
if len(@.Suffix) > 0 set @.NameString = rtrim(left(@.NameString, len(@.NameString) - len(@.TempString)))
if @.LastName is null
begin
--Get LastName and strip it out of the string
set @.LastName = ltrim(right(@.NameString, charindex(' ', Reverse(@.NameString) + ' ')))
set @.NameString = rtrim(left(@.NameString, len(@.NameString) - len(@.LastName)))
--Check to see if the last name has two parts
set @.TempString = ltrim(right(@.NameString, charindex(' ', Reverse(@.NameString) + ' ')))
if @.TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE')
begin
set @.LastName = @.TempString + ' ' + @.LastName
set @.NameString = rtrim(left(@.NameString, len(@.NameString) - len(@.TempString)))
end
end
--Get FirstName and strip it out of the string
set @.FirstName = rtrim(left(@.NameString, charindex(' ', @.NameString + ' ')))
set @.NameString = ltrim(right(@.NameString, len(@.NameString) - len(@.FirstName)))
--Anything remaining is MiddleName
set @.MiddleName = @.NameString
--Create the output string
set @.TempString = ''
while len(@.NameFormat) > 0
begin
if @.IgnorePeriod = 'F' or left(@.NameFormat, 1) <> '.'
begin
set @.IgnorePeriod = 'F'
set @.TempString = @.TempString +
case ascii(left(@.NameFormat, 1))
when '72' then case @.Honorific
when 'Dr' then 'Doctor'
when 'Rev' then 'Reverend'
when 'Hon' then 'Honorable'
when 'Maj' then 'Major'
when 'Pvt' then 'Private'
else isnull(@.Honorific, '')
end
when '70' then isnull(@.FirstName, '')
when '77' then isnull(@.MiddleName, '')
when '76' then isnull(@.LastName, '')
when '83' then case @.Suffix
when 'Jr' then 'Junior'
when 'Sr' then 'Senior'
when 'Esq' then 'Esquire'
else isnull(@.Suffix, '')
end
when '104' then case @.Honorific
when 'Doctor' then 'Dr'
when 'Reverend' then 'Rev'
when 'Honorable' then 'Hon'
when 'Major' then 'Maj'
when 'Private' then 'Pvt'
else isnull(@.Honorific, '')
end
when '102' then isnull(left(@.FirstName, 1), '')
when '109' then isnull(left(@.MiddleName, 1), '')
when '108' then isnull(left(@.LastName, 1), '')
when '115' then case @.Suffix
when 'Junior' then 'Jr'
when 'Senior' then 'Sr'
when 'Esquire' then 'Esq'
else isnull(@.Suffix, '')
end
when '46' then case right(@.TempString, 1)
when ' ' then ''
else '.'
end
when '44' then case right(@.TempString, 1)
when ' ' then ''
else ','
end
when '32' then case right(@.TempString, 1)
when ' ' then ''
else ' '
end
else ''
end
if ((ascii(left(@.NameFormat, 1)) = 72 and @.Honorific in ('FATHER', 'SISTER'))
or (ascii(left(@.NameFormat, 1)) = 115 and @.Suffix in ('II', 'III')))
set @.IgnorePeriod = 'T'
end
set @.NameFormat = right(@.NameFormat, len(@.NameFormat) - 1)
end
-- select ltrim(rtrim(@.TempString))
Return @.TempString
end
parsing XML string in a text field
I am trying to build a query on a SQL2000 text field which stores XML
string. The query is like "select requestnumber from history where
requestnumber is like '%re1%'". As you can see in the following sample
records, the xml string has database structure and the requestnumber is
a node of the XML. I wonder if it is possible to have SQL server parse
this field and allow me to do the query. If not, any suggestion would
be appreciated as to how to store XML data in SQL2000. I am not sure if
I misused the SQL2000 XML feature correctly. So far I pass the raw
query result to ADO and manipulate it in XMLDOM.
The table is to capture history of changes in any record in my
database. So I need to keep it simple so any record from any table can
be stored in here. The structure of the table is like this:
sysObjectNumber(int, not null)
recordKeyValues(char(30), not null)
archiveTime(datetime, not null)
history(text, null)
A sample record would be like the following:
sysObjectNumber recordKeyValues arvhiveTime History
=============== =============== =========== =======
1728725211 ABC 2005-03-25 8:09:56.700
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>user1
</usedby></history>
1728725211 ABC 2005-03-28 11:01:14.407
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user2</u
sedby></history>
1728725211 ABC 2005-03-28 11:46:12.723
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user1</
usedby></history>
1728725211 ABC 2005-03-28 11:46:35.273
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user4</
usedby></history>
<androidsun@.yahoo.com> wrote in message
news:1112727090.902677.100370@.l41g2000cwc.googlegr oups.com...
>I am trying to build a query on a SQL2000 text field which stores XML
> string. The query is like "select requestnumber from history where
> requestnumber is like '%re1%'". As you can see in the following sample
> records, the xml string has database structure and the requestnumber is
> a node of the XML. I wonder if it is possible to have SQL server parse
> this field and allow me to do the query. If not, any suggestion would
> be appreciated as to how to store XML data in SQL2000. I am not sure if
> I misused the SQL2000 XML feature correctly. So far I pass the raw
> query result to ADO and manipulate it in XMLDOM.
> The table is to capture history of changes in any record in my
> database. So I need to keep it simple so any record from any table can
> be stored in here. The structure of the table is like this:
> sysObjectNumber(int, not null)
> recordKeyValues(char(30), not null)
> archiveTime(datetime, not null)
> history(text, null)
> A sample record would be like the following:
> sysObjectNumber recordKeyValues arvhiveTime History
> =============== =============== =========== =======
> 1728725211 ABC 2005-03-25 8:09:56.700
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>use
r1</usedby></history>
> 1728725211 ABC 2005-03-28 11:01:14.407
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user2<
/usedby></history>
> 1728725211 ABC 2005-03-28 11:46:12.723
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user1
</usedby></history>
> 1728725211 ABC 2005-03-28 11:46:35.273
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user4
</usedby></history>
>
string. The query is like "select requestnumber from history where
requestnumber is like '%re1%'". As you can see in the following sample
records, the xml string has database structure and the requestnumber is
a node of the XML. I wonder if it is possible to have SQL server parse
this field and allow me to do the query. If not, any suggestion would
be appreciated as to how to store XML data in SQL2000. I am not sure if
I misused the SQL2000 XML feature correctly. So far I pass the raw
query result to ADO and manipulate it in XMLDOM.
The table is to capture history of changes in any record in my
database. So I need to keep it simple so any record from any table can
be stored in here. The structure of the table is like this:
sysObjectNumber(int, not null)
recordKeyValues(char(30), not null)
archiveTime(datetime, not null)
history(text, null)
A sample record would be like the following:
sysObjectNumber recordKeyValues arvhiveTime History
=============== =============== =========== =======
1728725211 ABC 2005-03-25 8:09:56.700
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>user1
</usedby></history>
1728725211 ABC 2005-03-28 11:01:14.407
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user2</u
sedby></history>
1728725211 ABC 2005-03-28 11:46:12.723
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user1</
usedby></history>
1728725211 ABC 2005-03-28 11:46:35.273
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user4</
usedby></history>
<androidsun@.yahoo.com> wrote in message
news:1112727090.902677.100370@.l41g2000cwc.googlegr oups.com...
>I am trying to build a query on a SQL2000 text field which stores XML
> string. The query is like "select requestnumber from history where
> requestnumber is like '%re1%'". As you can see in the following sample
> records, the xml string has database structure and the requestnumber is
> a node of the XML. I wonder if it is possible to have SQL server parse
> this field and allow me to do the query. If not, any suggestion would
> be appreciated as to how to store XML data in SQL2000. I am not sure if
> I misused the SQL2000 XML feature correctly. So far I pass the raw
> query result to ADO and manipulate it in XMLDOM.
> The table is to capture history of changes in any record in my
> database. So I need to keep it simple so any record from any table can
> be stored in here. The structure of the table is like this:
> sysObjectNumber(int, not null)
> recordKeyValues(char(30), not null)
> archiveTime(datetime, not null)
> history(text, null)
> A sample record would be like the following:
> sysObjectNumber recordKeyValues arvhiveTime History
> =============== =============== =========== =======
> 1728725211 ABC 2005-03-25 8:09:56.700
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>use
r1</usedby></history>
> 1728725211 ABC 2005-03-28 11:01:14.407
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user2<
/usedby></history>
> 1728725211 ABC 2005-03-28 11:46:12.723
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user1
</usedby></history>
> 1728725211 ABC 2005-03-28 11:46:35.273
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user4
</usedby></history>
>
parsing XML string in a text field
I am trying to build a query on a SQL2000 text field which stores XML
string. The query is like "select requestnumber from history where
requestnumber is like '%re1%'". As you can see in the following sample
records, the xml string has database structure and the requestnumber is
a node of the XML. I wonder if it is possible to have SQL server parse
this field and allow me to do the query. If not, any suggestion would
be appreciated as to how to store XML data in SQL2000. I am not sure if
I misused the SQL2000 XML feature correctly. So far I pass the raw
query result to ADO and manipulate it in XMLDOM.
The table is to capture history of changes in any record in my
database. So I need to keep it simple so any record from any table can
be stored in here. The structure of the table is like this:
sysObjectNumber(int, not null)
recordKeyValues(char(30), not null)
archiveTime(datetime, not null)
history(text, null)
A sample record would be like the following:
sysObjectNumber recordKeyValues arvhiveTime History
=============== =============== =========== =======
1728725211 ABC 2005-03-25 8:09:56.700
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastqu
antityin></lastquantityin><lastquantityout></lastquantityout><threshhold>nul
l</threshhold><usedby>user1
</usedby></history>
1728725211 ABC 2005-03-28 11:01:14.407
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastqu
antityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</
threshhold><usedby>user2</u
sedby></history>
1728725211 ABC 2005-03-28 11:46:12.723
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastq
uantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4<
/threshhold><usedby>user1</
usedby></history>
1728725211 ABC 2005-03-28 11:46:35.273
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastq
uantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4<
/threshhold><usedby>user4</
usedby></history><androidsun@.yahoo.com> wrote in message
news:1112727090.902677.100370@.l41g2000cwc.googlegroups.com...
>I am trying to build a query on a SQL2000 text field which stores XML
> string. The query is like "select requestnumber from history where
> requestnumber is like '%re1%'". As you can see in the following sample
> records, the xml string has database structure and the requestnumber is
> a node of the XML. I wonder if it is possible to have SQL server parse
> this field and allow me to do the query. If not, any suggestion would
> be appreciated as to how to store XML data in SQL2000. I am not sure if
> I misused the SQL2000 XML feature correctly. So far I pass the raw
> query result to ADO and manipulate it in XMLDOM.
> The table is to capture history of changes in any record in my
> database. So I need to keep it simple so any record from any table can
> be stored in here. The structure of the table is like this:
> sysObjectNumber(int, not null)
> recordKeyValues(char(30), not null)
> archiveTime(datetime, not null)
> history(text, null)
> A sample record would be like the following:
> sysObjectNumber recordKeyValues arvhiveTime History
> =============== =============== =========== =======
> 1728725211 ABC 2005-03-25 8:09:56.700
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></las
tquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>
use
r1</usedby></history>
> 1728725211 ABC 2005-03-28 11:01:14.407
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></las
tquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>use
r2<
/usedby></history>
> 1728725211 ABC 2005-03-28 11:46:12.723
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></la
stquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>us
er1
</usedby></history>
> 1728725211 ABC 2005-03-28 11:46:35.273
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></la
stquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>us
er4
</usedby></history>
>
string. The query is like "select requestnumber from history where
requestnumber is like '%re1%'". As you can see in the following sample
records, the xml string has database structure and the requestnumber is
a node of the XML. I wonder if it is possible to have SQL server parse
this field and allow me to do the query. If not, any suggestion would
be appreciated as to how to store XML data in SQL2000. I am not sure if
I misused the SQL2000 XML feature correctly. So far I pass the raw
query result to ADO and manipulate it in XMLDOM.
The table is to capture history of changes in any record in my
database. So I need to keep it simple so any record from any table can
be stored in here. The structure of the table is like this:
sysObjectNumber(int, not null)
recordKeyValues(char(30), not null)
archiveTime(datetime, not null)
history(text, null)
A sample record would be like the following:
sysObjectNumber recordKeyValues arvhiveTime History
=============== =============== =========== =======
1728725211 ABC 2005-03-25 8:09:56.700
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastqu
antityin></lastquantityin><lastquantityout></lastquantityout><threshhold>nul
l</threshhold><usedby>user1
</usedby></history>
1728725211 ABC 2005-03-28 11:01:14.407
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastqu
antityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</
threshhold><usedby>user2</u
sedby></history>
1728725211 ABC 2005-03-28 11:46:12.723
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastq
uantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4<
/threshhold><usedby>user1</
usedby></history>
1728725211 ABC 2005-03-28 11:46:35.273
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastq
uantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4<
/threshhold><usedby>user4</
usedby></history><androidsun@.yahoo.com> wrote in message
news:1112727090.902677.100370@.l41g2000cwc.googlegroups.com...
>I am trying to build a query on a SQL2000 text field which stores XML
> string. The query is like "select requestnumber from history where
> requestnumber is like '%re1%'". As you can see in the following sample
> records, the xml string has database structure and the requestnumber is
> a node of the XML. I wonder if it is possible to have SQL server parse
> this field and allow me to do the query. If not, any suggestion would
> be appreciated as to how to store XML data in SQL2000. I am not sure if
> I misused the SQL2000 XML feature correctly. So far I pass the raw
> query result to ADO and manipulate it in XMLDOM.
> The table is to capture history of changes in any record in my
> database. So I need to keep it simple so any record from any table can
> be stored in here. The structure of the table is like this:
> sysObjectNumber(int, not null)
> recordKeyValues(char(30), not null)
> archiveTime(datetime, not null)
> history(text, null)
> A sample record would be like the following:
> sysObjectNumber recordKeyValues arvhiveTime History
> =============== =============== =========== =======
> 1728725211 ABC 2005-03-25 8:09:56.700
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></las
tquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>
use
r1</usedby></history>
> 1728725211 ABC 2005-03-28 11:01:14.407
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></las
tquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>use
r2<
/usedby></history>
> 1728725211 ABC 2005-03-28 11:46:12.723
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></la
stquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>us
er1
</usedby></history>
> 1728725211 ABC 2005-03-28 11:46:35.273
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></la
stquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>us
er4
</usedby></history>
>
Wednesday, March 28, 2012
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
> --
>
>
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 Text String Field - comma delimited
Can anyone tell me how to parse out a comma-delimited text string field for reporting purposes? What I need is for each new value within the string to create a new record.
Example:
ID STRING
12345 1,2,3
67891 2,4
Becomes
12345 1
12345 2
12345 3
67891 2
67891 4
Thanks for any help!!
Check this page. It has great examples (if you are using 2005 there are two really great ways of handling this)
http://www.aspfaq.com/show.asp?id=2529
Parsing Text String Field
Can anyone tell me how to parse out a comma-delimited text string field for reporting purposes?
Example:
ID STRING
12345 1,2,3
67891 2,4
Becomes
12345 1
12345 2
12345 3
67891 2
67891 4
Thanks for any help!!
You should be handling this in the Dataset not in SSRS.
If you need help with SQL statements to to do this I'm sure posting to the Transact-SQL forum will provide a response on how to get a dataset to come back with the relevant rows. You may have to use functions, sp's, or temporary tables to get the data back in that format.
Craig
Parsing Text String Field
Can anyone tell me how to parse out a comma-delimited text string field for reporting purposes?
Example:
ID STRING
12345 1,2,3
67891 2,4
Becomes
12345 1
12345 2
12345 3
67891 2
67891 4
Thanks for any help!!
You should be handling this in the Dataset not in SSRS.
If you need help with SQL statements to to do this I'm sure posting to the Transact-SQL forum will provide a response on how to get a dataset to come back with the relevant rows. You may have to use functions, sp's, or temporary tables to get the data back in that format.
Craig
Parsing string and Inserting each element?
Hi All,
I'll try and make this simple.
I have a column in a table that has one or more values separated by a comma.
Ex: 1234,456,322,33445,abce,ekksks
I want to go through each record and take this column data and parse it out,
then insert each element into another table.
ex:
INSERT Into GTable (RecordID, ItemValue)
Values (NewID(), <Parsed Values from each record's
Column> )
Any ideas would be greatly appreciated.
John.Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
How do I simulate an array inside a stored procedure?
http://www.aspfaq.com/show.asp?id=2248
AMB
"John Rugo" wrote:
> Hi All,
> I'll try and make this simple.
> I have a column in a table that has one or more values separated by a comm
a.
> Ex: 1234,456,322,33445,abce,ekksks
> I want to go through each record and take this column data and parse it ou
t,
> then insert each element into another table.
> ex:
> INSERT Into GTable (RecordID, ItemValue)
> Values (NewID(), <Parsed Values from each record's
> Column> )
> Any ideas would be greatly appreciated.
> John.
>
>|||Do you mean inserting each value as a separate row? If I were to create the
insert statements manually for your example, would it look like:
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '1234')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '456')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '322')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '33445')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), 'abce')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), 'ekksks')
?
If so, then a cursor would probably be the best way to go (hopefully this is
a one time thing, because as we all know, cursors as the devil)...
If you want some quickly hacked code as an example of how the cursor would
work, post back and I'll see if I can put something together..
-Cliff
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:Ot0XwOsPFHA.4028@.tk2msftngp13.phx.gbl...
> Hi All,
> I'll try and make this simple.
> I have a column in a table that has one or more values separated by a
comma.
> Ex: 1234,456,322,33445,abce,ekksks
> I want to go through each record and take this column data and parse it
out,
> then insert each element into another table.
> ex:
> INSERT Into GTable (RecordID, ItemValue)
> Values (NewID(), <Parsed Values from each record's
> Column> )
> Any ideas would be greatly appreciated.
> John.
>|||John,
Here is an efficient way to do this - it's buried in one of the articles
Alejandro referred you to.
/*
A table-valued function with one parameter, a delimited list,
that returns the separate distinct items of the list.
Steve Kass, Drew University
Thanks to MVPs Linda Wierzbicki and Umachandar Jayachandran
for help and helpful discussions on this.
*/
--A table of integers is needed
create table Seq (
Nbr int not null
)
insert into Seq
select top 4001 0
from Northwind..[Order Details]
cross join (select 1 as n union all select 2) X
declare @.i int
set @.i = -1
update Seq
set @.i = Nbr = @.i + 1
alter table Seq add constraint pk_Seq primary key (Nbr)
--table Seq created
go
--This makes things more readable. The list is easier
--to process if it begins and ends with a single comma
--As it turns out also, list items cannot
--have leading or trailing spaces (here any leading spaces
--in the first item or trailing spaces in the last are
--eliminated)
create function RegularizedList (@.List varchar(8000))
returns varchar(8000) as begin
return replace(rtrim(','+ltrim(@.List))+',', ',,', ',')
end
go
--This function returns a table containing one column, commaPos,
--of integers, the positions of each comma in @.List, except the last
--This function returns a table containing the items in the list.
--The items are extracted by selecting those substrings of
--the list that begin immediately after a comma and end
--immediately before the next comma, then trimming spaces on
--both sides.
create function ListTable (@.List varchar(8000))
returns table as return
select
ltrim(rtrim(
substring(regL,
commaPos+1,
charindex(',', regL, commaPos+1) - (commaPos+1))))
as Item
from (
select Nbr as commaPos
from Seq, (
select dbo.RegularizedList(@.List) as regL
) R
where substring(regL,Nbr,1) = ','
and Nbr < len(regL)
) L, (
select dbo.RegularizedList(@.List) as regL
) R
go
--examples
declare @.x varchar(4000), @.time datetime
set @.time = getdate()
set @.x = replicate('foo,bar,foo,bar,ab,',30) + 'end'
select distinct Item from ListTable(@.x)
select datediff(ms,@.time,getdate())
set @.x = '10245 10345 98292 '
declare @.s varchar(400)
set @.s = replace(@.x,' ',',')
select * from ListTable(@.s)
--Note, if a list contains a non-comma delimiter, and contains no
--commas within items, this replacement allows the function to
--handle it. If a comma appears in an item, but some other non-
--delimiter is absent from the list, a three-step replacement can
--be made:
-- replace all commas with new character not in list
-- replace all delimiters with comma
-- Use (select replace(Item,<new>,<comma> ) from ListTable(@.List)) LT
-- where the list table is used.
go
--Since this is a repro script, delete everything!
--Keep them around if they are helpful, though.
DROP FUNCTION RegularizedList
DROP TABLE Seq
DROP FUNCTION ListTable
-- Steve Kass
-- Drew University
John Rugo wrote:
>Hi All,
>I'll try and make this simple.
>I have a column in a table that has one or more values separated by a comma
.
>Ex: 1234,456,322,33445,abce,ekksks
>I want to go through each record and take this column data and parse it out
,
>then insert each element into another table.
>ex:
>INSERT Into GTable (RecordID, ItemValue)
> Values (NewID(), <Parsed Values from each record's
>Column> )
>Any ideas would be greatly appreciated.
>John.
>
>
I'll try and make this simple.
I have a column in a table that has one or more values separated by a comma.
Ex: 1234,456,322,33445,abce,ekksks
I want to go through each record and take this column data and parse it out,
then insert each element into another table.
ex:
INSERT Into GTable (RecordID, ItemValue)
Values (NewID(), <Parsed Values from each record's
Column> )
Any ideas would be greatly appreciated.
John.Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
How do I simulate an array inside a stored procedure?
http://www.aspfaq.com/show.asp?id=2248
AMB
"John Rugo" wrote:
> Hi All,
> I'll try and make this simple.
> I have a column in a table that has one or more values separated by a comm
a.
> Ex: 1234,456,322,33445,abce,ekksks
> I want to go through each record and take this column data and parse it ou
t,
> then insert each element into another table.
> ex:
> INSERT Into GTable (RecordID, ItemValue)
> Values (NewID(), <Parsed Values from each record's
> Column> )
> Any ideas would be greatly appreciated.
> John.
>
>|||Do you mean inserting each value as a separate row? If I were to create the
insert statements manually for your example, would it look like:
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '1234')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '456')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '322')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), '33445')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), 'abce')
INSERT INTO(GTable (RecordID, ItemValue)
Values (NewID(), 'ekksks')
?
If so, then a cursor would probably be the best way to go (hopefully this is
a one time thing, because as we all know, cursors as the devil)...
If you want some quickly hacked code as an example of how the cursor would
work, post back and I'll see if I can put something together..
-Cliff
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:Ot0XwOsPFHA.4028@.tk2msftngp13.phx.gbl...
> Hi All,
> I'll try and make this simple.
> I have a column in a table that has one or more values separated by a
comma.
> Ex: 1234,456,322,33445,abce,ekksks
> I want to go through each record and take this column data and parse it
out,
> then insert each element into another table.
> ex:
> INSERT Into GTable (RecordID, ItemValue)
> Values (NewID(), <Parsed Values from each record's
> Column> )
> Any ideas would be greatly appreciated.
> John.
>|||John,
Here is an efficient way to do this - it's buried in one of the articles
Alejandro referred you to.
/*
A table-valued function with one parameter, a delimited list,
that returns the separate distinct items of the list.
Steve Kass, Drew University
Thanks to MVPs Linda Wierzbicki and Umachandar Jayachandran
for help and helpful discussions on this.
*/
--A table of integers is needed
create table Seq (
Nbr int not null
)
insert into Seq
select top 4001 0
from Northwind..[Order Details]
cross join (select 1 as n union all select 2) X
declare @.i int
set @.i = -1
update Seq
set @.i = Nbr = @.i + 1
alter table Seq add constraint pk_Seq primary key (Nbr)
--table Seq created
go
--This makes things more readable. The list is easier
--to process if it begins and ends with a single comma
--As it turns out also, list items cannot
--have leading or trailing spaces (here any leading spaces
--in the first item or trailing spaces in the last are
--eliminated)
create function RegularizedList (@.List varchar(8000))
returns varchar(8000) as begin
return replace(rtrim(','+ltrim(@.List))+',', ',,', ',')
end
go
--This function returns a table containing one column, commaPos,
--of integers, the positions of each comma in @.List, except the last
--This function returns a table containing the items in the list.
--The items are extracted by selecting those substrings of
--the list that begin immediately after a comma and end
--immediately before the next comma, then trimming spaces on
--both sides.
create function ListTable (@.List varchar(8000))
returns table as return
select
ltrim(rtrim(
substring(regL,
commaPos+1,
charindex(',', regL, commaPos+1) - (commaPos+1))))
as Item
from (
select Nbr as commaPos
from Seq, (
select dbo.RegularizedList(@.List) as regL
) R
where substring(regL,Nbr,1) = ','
and Nbr < len(regL)
) L, (
select dbo.RegularizedList(@.List) as regL
) R
go
--examples
declare @.x varchar(4000), @.time datetime
set @.time = getdate()
set @.x = replicate('foo,bar,foo,bar,ab,',30) + 'end'
select distinct Item from ListTable(@.x)
select datediff(ms,@.time,getdate())
set @.x = '10245 10345 98292 '
declare @.s varchar(400)
set @.s = replace(@.x,' ',',')
select * from ListTable(@.s)
--Note, if a list contains a non-comma delimiter, and contains no
--commas within items, this replacement allows the function to
--handle it. If a comma appears in an item, but some other non-
--delimiter is absent from the list, a three-step replacement can
--be made:
-- replace all commas with new character not in list
-- replace all delimiters with comma
-- Use (select replace(Item,<new>,<comma> ) from ListTable(@.List)) LT
-- where the list table is used.
go
--Since this is a repro script, delete everything!
--Keep them around if they are helpful, though.
DROP FUNCTION RegularizedList
DROP TABLE Seq
DROP FUNCTION ListTable
-- Steve Kass
-- Drew University
John Rugo wrote:
>Hi All,
>I'll try and make this simple.
>I have a column in a table that has one or more values separated by a comma
.
>Ex: 1234,456,322,33445,abce,ekksks
>I want to go through each record and take this column data and parse it out
,
>then insert each element into another table.
>ex:
>INSERT Into GTable (RecordID, ItemValue)
> Values (NewID(), <Parsed Values from each record's
>Column> )
>Any ideas would be greatly appreciated.
>John.
>
>
Parse values from delimited string
Hi. I have a string like this which will be passed into a SQL Server 2000 stored procedure, Exam ID Branch Number 15438 39 21347 96 24198 23 
|||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.
[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.
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
|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 sting with quotes in SQL
If, I have a string as follows , how Do I parse these quotes, such that it gives me 3 fields.
Eg of string is
Status changes "PASS" "FAIL" By "PW" '"/3/12/2007"
Resuls set should look like:
Column 1 Column 2 Column 3
PASS FAIL 3/12/2007
Pl advise.
Use a combination of the replace() function and Jen Suessmeyer's split function.
If there are no 'spaces' in the data, a single pass replacing the double quote with an empty string would work.
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
Rob|||
this is a code to break a string "first"Second"third"Forth"Fifth"Sixth"" into First Second third forth etc however u have to pass and Extra " in the end of the string
declare @.Group varchar(8000)
Create table #Temp(GroupName varchar(100))
Declare @.Cnt int,@.CharLocation int,@.Previous int,@.Extent int
Set @.Group='"First"Second"Third"Forth"Fifth"Sixth""'
Set @.Cnt=1
Set @.Previous=1
Set @.Extent=1
While @.Cnt<>Len(@.Group)
BEgin
if substring(@.Group,@.Cnt,1)='"'
BEgin
--Select Substring(@.GroupName,@.Previous,@.Extent-1)
insert into #Temp values(Substring(@.Group,@.Previous,@.Extent-1))
sET @.Previous=@.Cnt+1
Set @.Extent=0
END
Set @.Extent=@.Extent+1
Set @.Cnt=@.Cnt+1
END
select * from #temp where GroupName <>''
drop table #Temp
try doing some manipulations and see if this code is of any help
RegarDs,
Jacx
|||I need this to be split into columns, the code above splits it into rows|||Jen's Split function I offered does split into columns -but of course you didn't try it, did you?|||I did try Jens split function. I created that in the dbo, BUT it created that function as a type='TF', Not 'FN'.
So when I actually call the function, it gives err message invalid object.
This is how I call the Split Function .
select dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')
and it errs out with the message below, though the split object does exist in the db. Any suggestions.
Thanks much.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.Split'.
You made a simple error. The function is a table valued function (TVF or "TF") That means that you use it like a table.
Instead of:
select dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')
Try:
Also, recall my earlier comment about needing to use the replace() function to remove the quotes and/or commas.|||SELECT * FROM dbo.Split( '"mod PEN" "approved" "by wagety" "3/21/2007"','""')
I have 2 issues:
1. I need the string split into multiple columns, not just 1. ie COL1 Shoud have "mod PEN" ,
Col2 should hv "approv" Col3 should hv "by wagety" and Col4 "3/21/2007"'. This split function returns everthying in 1 column.
2. Also, I have these values that are to be split, stored in a table. eg there is a table called notes, with a field called status.
That field status will have that records, which will have values such as "mod PEN" "approved" "by wagety" "3/21/2007"' this entire string will be 1 row. How can I use this function, for that tbl notes?
|||
1. I thought I understood that was your request.
Jen's split function will separte into different columns, if you are having difficulty, you may not have identified and be handling the delimiters correctly.
2. You have completely confused me now.
I previously understood that you wanted to separate
into separate parts -at least that is what your example seemed to indicate that you wanted.
"PASS" "FAIL" By "PW" '"/3/12/2007"
Now, it seems that you want to store all of the parts in one field called 'Status'.
Why not just store the current string in the Status field and be done?
|||1. This is how I call Jens split function and it return all the values In 1 Single Column: eg
select * from dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')
When you execute this, it returns the following , into 1 Column called SplitValue . I need the values between the doubel quotes, split into different columns, ie in this case 4 diff cols, ie Col1 should have mod PEN, Col2 should have approved, Colum # 3 shd have By wagety, Col 4 should have 3/21/2007. Can you pl assist
Output returns the foll:
OccurenceId SplitValue
-- --
1 "mod PEN" "approved" "by wagety" "3/21/2007"
Parse sting with quotes in SQL
If, I have a string as follows , how Do I parse these quotes, such that it gives me 3 fields.
Eg of string is
Status changes "PASS" "FAIL" By "PW" '"/3/12/2007"
Resuls set should look like:
Column 1 Column 2 Column 3
PASS FAIL 3/12/2007
Pl advise.
Use a combination of the replace() function and Jen Suessmeyer's split function.
If there are no 'spaces' in the data, a single pass replacing the double quote with an empty string would work.
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
|||Do you really need them in separate columns? If rows will do, then you could use Itzik's split function. Check insidetsql.com for the script.
Rob|||
this is a code to break a string "first"Second"third"Forth"Fifth"Sixth"" into First Second third forth etc however u have to pass and Extra " in the end of the string
declare @.Group varchar(8000)
Create table #Temp(GroupName varchar(100))
Declare @.Cnt int,@.CharLocation int,@.Previous int,@.Extent int
Set @.Group='"First"Second"Third"Forth"Fifth"Sixth""'
Set @.Cnt=1
Set @.Previous=1
Set @.Extent=1
While @.Cnt<>Len(@.Group)
BEgin
if substring(@.Group,@.Cnt,1)='"'
BEgin
--Select Substring(@.GroupName,@.Previous,@.Extent-1)
insert into #Temp values(Substring(@.Group,@.Previous,@.Extent-1))
sET @.Previous=@.Cnt+1
Set @.Extent=0
END
Set @.Extent=@.Extent+1
Set @.Cnt=@.Cnt+1
END
select * from #temp where GroupName <>''
drop table #Temp
try doing some manipulations and see if this code is of any help
RegarDs,
Jacx
|||I need this to be split into columns, the code above splits it into rows|||Jen's Split function I offered does split into columns -but of course you didn't try it, did you?|||I did try Jens split function. I created that in the dbo, BUT it created that function as a type='TF', Not 'FN'.
So when I actually call the function, it gives err message invalid object.
This is how I call the Split Function .
select dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')
and it errs out with the message below, though the split object does exist in the db. Any suggestions.
Thanks much.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.Split'.
You made a simple error. The function is a table valued function (TVF or "TF") That means that you use it like a table.
Instead of:
select dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')
Try:
Also, recall my earlier comment about needing to use the replace() function to remove the quotes and/or commas.|||SELECT * FROM dbo.Split( '"mod PEN" "approved" "by wagety" "3/21/2007"','""')
I have 2 issues:
1. I need the string split into multiple columns, not just 1. ie COL1 Shoud have "mod PEN" ,
Col2 should hv "approv" Col3 should hv "by wagety" and Col4 "3/21/2007"'. This split function returns everthying in 1 column.
2. Also, I have these values that are to be split, stored in a table. eg there is a table called notes, with a field called status.
That field status will have that records, which will have values such as "mod PEN" "approved" "by wagety" "3/21/2007"' this entire string will be 1 row. How can I use this function, for that tbl notes?
|||
1. I thought I understood that was your request.
Jen's split function will separte into different columns, if you are having difficulty, you may not have identified and be handling the delimiters correctly.
2. You have completely confused me now.
I previously understood that you wanted to separate
into separate parts -at least that is what your example seemed to indicate that you wanted."PASS" "FAIL" By "PW" '"/3/12/2007"
Now, it seems that you want to store all of the parts in one field called 'Status'.
Why not just store the current string in the Status field and be done?
|||1. This is how I call Jens split function and it return all the values In 1 Single Column: eg
select * from dbo.Split ('"mod PEN" "approved" "by wagety" "3/21/2007"','""')
When you execute this, it returns the following , into 1 Column called SplitValue . I need the values between the doubel quotes, split into different columns, ie in this case 4 diff cols, ie Col1 should have mod PEN, Col2 should have approved, Colum # 3 shd have By wagety, Col 4 should have 3/21/2007. Can you pl assist
Output returns the foll:
OccurenceId SplitValue
-- --
1 "mod PEN" "approved" "by wagety" "3/21/2007"
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
>> >>
>> >>
>> >>
>>
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:
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:
Wednesday, March 21, 2012
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:
>
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 comma separated string into individual rows
All,
Can anyone help me in this task? I have a table named 'Activity' which has two columns: ActivityID and ActivityDetail. Sample rows look like this:
ActivityID ActivityDetail
1 'Football','Basket ball', Tennis'
2 'Basket ball', 'Volley ball'
I would like to have a stored procedure which parses the data into a new table (e.g. named as 'Activity_breadkdown' and with an identity column named 'DetailID') and looks like this:
DetailID ActivityID ActivityDetail
1 1 Football
2 1 Basket ball
3 1 Tennis
4 2 Basket ball
5 2 Volley ball
Thanks in advance
AlYou can modify the code posted in this thread...
http://www.dbforums.com/t974750.html
Can anyone help me in this task? I have a table named 'Activity' which has two columns: ActivityID and ActivityDetail. Sample rows look like this:
ActivityID ActivityDetail
1 'Football','Basket ball', Tennis'
2 'Basket ball', 'Volley ball'
I would like to have a stored procedure which parses the data into a new table (e.g. named as 'Activity_breadkdown' and with an identity column named 'DetailID') and looks like this:
DetailID ActivityID ActivityDetail
1 1 Football
2 1 Basket ball
3 1 Tennis
4 2 Basket ball
5 2 Volley ball
Thanks in advance
AlYou can modify the code posted in this thread...
http://www.dbforums.com/t974750.html
Parse a numeric string from a field
Hello All,
I'm trying to parse for a numeric string from a column in a table. What
I'm looking for is a numeric string of a fixed length of 8.
The column is a comments field and can contain the numeric string in
any position
Here's an example of the values in the column
1) Fri KX 3-21-98 5:48 P.M. arrival Cxled ATRI #27068935 3-17-98
2) wed.kx10/26 Netrez 95860536
Now I need to parse through these lines and return only the 8 digit
numbers in it
The result set should be
27068935
95860536
This is what I've done so far
Declare @.tmp table
(
Comments_Txt varchar(255)
)
Insert into @.tmp
select Comments_Txt from Reservation
select * FROM @.tmp where Comments_Txt
like ('%[0-9][0-9][0-9][0-9][0-9][0**9]%')
But it returns the entire comments field in the result set. What I need
is a way to return just those 8 digits.
Any Ideas??
Thanks in advance!!!You could use the following:
select substring(comments_txt,
patindex('%[0-9][0-9][0-9][0-9][0-9][0***9]%', comments_txt), 8)
from @.tmp where Comments_Txt
like ('%[0-9][0-9][0-9][0-9][0-9][0***9]%')
--
David Rowland
dbmonitor.tripod.com|||Thank You very much !!! That was just what I needed.
Friday, March 9, 2012
Parameters in RS
Hi All,
Wats the maximun size for the parameters in SQl'
I have a string parameter and if a paa about 130 characters... Its getting
truncated ...
Any help please..
Thanks,depends on the datatype but I think 8000 for a char or varchar
also depends on case 4000 if you are case dependent
"CCP" wrote:
> Hi All,
> Wats the maximun size for the parameters in SQl'
> I have a string parameter and if a paa about 130 characters... Its getting
> truncated ...
>
> Any help please..
>
> Thanks,|||Unless, of course, the parameter is being passed via the querystring, at
which point it is restricted by the maximum length of the URL:
http://support.microsoft.com/default.aspx?scid=kb;en-us;208427
Mike G.
"letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
news:AF5AFF64-8683-42F5-BBE3-47EECCC31C98@.microsoft.com...
> depends on the datatype but I think 8000 for a char or varchar
> also depends on case 4000 if you are case dependent
>
> "CCP" wrote:
>> Hi All,
>> Wats the maximun size for the parameters in SQl'
>> I have a string parameter and if a paa about 130 characters... Its
>> getting
>> truncated ...
>>
>> Any help please..
>>
>> Thanks,|||Hi ,
Thanks for the quick response.
Im not passing them in the query string...
I could execute the sp in the query analyser without any probs...
only when i run the report the parameter is getting truncated...
not sure where the prob is'
Any help please...
"Mike G." wrote:
> Unless, of course, the parameter is being passed via the querystring, at
> which point it is restricted by the maximum length of the URL:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;208427
> Mike G.
>
> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> news:AF5AFF64-8683-42F5-BBE3-47EECCC31C98@.microsoft.com...
> > depends on the datatype but I think 8000 for a char or varchar
> >
> > also depends on case 4000 if you are case dependent
> >
> >
> >
> > "CCP" wrote:
> >
> >> Hi All,
> >> Wats the maximun size for the parameters in SQl'
> >> I have a string parameter and if a paa about 130 characters... Its
> >> getting
> >> truncated ...
> >>
> >>
> >> Any help please..
> >>
> >>
> >> Thanks,
>
>
Wats the maximun size for the parameters in SQl'
I have a string parameter and if a paa about 130 characters... Its getting
truncated ...
Any help please..
Thanks,depends on the datatype but I think 8000 for a char or varchar
also depends on case 4000 if you are case dependent
"CCP" wrote:
> Hi All,
> Wats the maximun size for the parameters in SQl'
> I have a string parameter and if a paa about 130 characters... Its getting
> truncated ...
>
> Any help please..
>
> Thanks,|||Unless, of course, the parameter is being passed via the querystring, at
which point it is restricted by the maximum length of the URL:
http://support.microsoft.com/default.aspx?scid=kb;en-us;208427
Mike G.
"letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
news:AF5AFF64-8683-42F5-BBE3-47EECCC31C98@.microsoft.com...
> depends on the datatype but I think 8000 for a char or varchar
> also depends on case 4000 if you are case dependent
>
> "CCP" wrote:
>> Hi All,
>> Wats the maximun size for the parameters in SQl'
>> I have a string parameter and if a paa about 130 characters... Its
>> getting
>> truncated ...
>>
>> Any help please..
>>
>> Thanks,|||Hi ,
Thanks for the quick response.
Im not passing them in the query string...
I could execute the sp in the query analyser without any probs...
only when i run the report the parameter is getting truncated...
not sure where the prob is'
Any help please...
"Mike G." wrote:
> Unless, of course, the parameter is being passed via the querystring, at
> which point it is restricted by the maximum length of the URL:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;208427
> Mike G.
>
> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> news:AF5AFF64-8683-42F5-BBE3-47EECCC31C98@.microsoft.com...
> > depends on the datatype but I think 8000 for a char or varchar
> >
> > also depends on case 4000 if you are case dependent
> >
> >
> >
> > "CCP" wrote:
> >
> >> Hi All,
> >> Wats the maximun size for the parameters in SQl'
> >> I have a string parameter and if a paa about 130 characters... Its
> >> getting
> >> truncated ...
> >>
> >>
> >> Any help please..
> >>
> >>
> >> Thanks,
>
>
Wednesday, March 7, 2012
Parameters formatting
Hello Everyone,
Is it possible to format the textboxes that are used for parameters.
For example a string field is double the length we require. We show 9
characters but it show 27 characters.
This is a great product, the user friendliness of the parameters is
causing issues with the directors and senior management.
Please help me as I want to keep using this.
Thanks
MichaelI posted a sample yesterday on www.MSBICentral.com
The sample is Parameters.FormattedDates.RDL... It shows how to format a date
for the parameters... I think this will show you how to do what you wish..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Michael van der Veeke" <michael@.property4view.com> wrote in message
news:uNVrXYyEFHA.624@.TK2MSFTNGP15.phx.gbl...
> Hello Everyone,
> Is it possible to format the textboxes that are used for parameters. For
> example a string field is double the length we require. We show 9
> characters but it show 27 characters.
> This is a great product, the user friendliness of the parameters is
> causing issues with the directors and senior management.
> Please help me as I want to keep using this.
> Thanks
> Michael
Is it possible to format the textboxes that are used for parameters.
For example a string field is double the length we require. We show 9
characters but it show 27 characters.
This is a great product, the user friendliness of the parameters is
causing issues with the directors and senior management.
Please help me as I want to keep using this.
Thanks
MichaelI posted a sample yesterday on www.MSBICentral.com
The sample is Parameters.FormattedDates.RDL... It shows how to format a date
for the parameters... I think this will show you how to do what you wish..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Michael van der Veeke" <michael@.property4view.com> wrote in message
news:uNVrXYyEFHA.624@.TK2MSFTNGP15.phx.gbl...
> Hello Everyone,
> Is it possible to format the textboxes that are used for parameters. For
> example a string field is double the length we require. We show 9
> characters but it show 27 characters.
> This is a great product, the user friendliness of the parameters is
> causing issues with the directors and senior management.
> Please help me as I want to keep using this.
> Thanks
> Michael
Monday, February 20, 2012
parameterized query string in Openrowset
Hi,
I have stored procedure in which i'm using the OpenRowSet method to fetch
data from an ODBC datasource.
The OpenRowSet method takes a query string to query the source.
I would like to add the parameters being passed to the calling stored
procedure to this query string.
unfortunately OPenRowSet does not accept string concatenation.
any suggestions on how to accomplish this would be really helpful.
Regards,
nabeelAccording to BOL , OPENROWSET doesn't accept variables. have you tried with
dynamic sql?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
news:516445D3-DE8B-40FA-9B70-D49107BCB382@.microsoft.com...
> Hi,
> I have stored procedure in which i'm using the OpenRowSet method to fetch
> data from an ODBC datasource.
> The OpenRowSet method takes a query string to query the source.
> I would like to add the parameters being passed to the calling stored
> procedure to this query string.
> unfortunately OPenRowSet does not accept string concatenation.
> any suggestions on how to accomplish this would be really helpful.
> Regards,
> nabeel|||Jack I'm not very proficient on T-SQL
can you please give me an example.
Thanks
"Jack Vamvas" wrote:
> According to BOL , OPENROWSET doesn't accept variables. have you tried wit
h
> dynamic sql?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
> news:516445D3-DE8B-40FA-9B70-D49107BCB382@.microsoft.com...
>
>|||Jack I'm not very proficient on T-SQL
can you please give me an example.
Thanks
"Jack Vamvas" wrote:
> According to BOL , OPENROWSET doesn't accept variables. have you tried wit
h
> dynamic sql?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
> news:516445D3-DE8B-40FA-9B70-D49107BCB382@.microsoft.com...
>
>
I have stored procedure in which i'm using the OpenRowSet method to fetch
data from an ODBC datasource.
The OpenRowSet method takes a query string to query the source.
I would like to add the parameters being passed to the calling stored
procedure to this query string.
unfortunately OPenRowSet does not accept string concatenation.
any suggestions on how to accomplish this would be really helpful.
Regards,
nabeelAccording to BOL , OPENROWSET doesn't accept variables. have you tried with
dynamic sql?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
news:516445D3-DE8B-40FA-9B70-D49107BCB382@.microsoft.com...
> Hi,
> I have stored procedure in which i'm using the OpenRowSet method to fetch
> data from an ODBC datasource.
> The OpenRowSet method takes a query string to query the source.
> I would like to add the parameters being passed to the calling stored
> procedure to this query string.
> unfortunately OPenRowSet does not accept string concatenation.
> any suggestions on how to accomplish this would be really helpful.
> Regards,
> nabeel|||Jack I'm not very proficient on T-SQL
can you please give me an example.
Thanks
"Jack Vamvas" wrote:
> According to BOL , OPENROWSET doesn't accept variables. have you tried wit
h
> dynamic sql?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
> news:516445D3-DE8B-40FA-9B70-D49107BCB382@.microsoft.com...
>
>|||Jack I'm not very proficient on T-SQL
can you please give me an example.
Thanks
"Jack Vamvas" wrote:
> According to BOL , OPENROWSET doesn't accept variables. have you tried wit
h
> dynamic sql?
> --
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Nabeel Moeen" <NabeelMoeen@.discussions.microsoft.com> wrote in message
> news:516445D3-DE8B-40FA-9B70-D49107BCB382@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)