How to insert on table with a variable name

Here is the jist of what I want to do, but I know the syntax is wrong...please help

CREATE PROCEDURE mySP
(
@.tablename As varChar(50),
@.input_name As varChar(100)
)
As
INSERT @.tablename (name)
VALUES (@.input_name)

assume the table exists already, and has a 'name' field in it.

thanks guysKatie,

I can't see why you would want to do that. However, the exact code would be:

exec ('INSERT INTO ' + @.tableName + ' VALUES (' + @.input_name + ')')

Be aware that

a) You really should avoid using 'exec' where possible since 'exec' will hinder Sql Server in pre-compiling the Stored Procedure
b) You really want to use a column-list with insert.

regards,

Kristof|||Originally posted by beyond cool
Katie,

I can't see why you would want to do that. However, the exact code would be:

exec ('INSERT INTO ' + @.tableName + ' VALUES (' + @.input_name + ')')

Be aware that

a) You really should avoid using 'exec' where possible since 'exec' will hinder Sql Server in pre-compiling the Stored Procedure
b) You really want to use a column-list with insert.

regards,

Kristof

is there a way around using 'exec'?|||Originally posted by beyond cool
Katie,

I can't see why you would want to do that. However, the exact code would be:

exec ('INSERT INTO ' + @.tableName + ' VALUES (' + @.input_name + ')')

Be aware that

a) You really should avoid using 'exec' where possible since 'exec' will hinder Sql Server in pre-compiling the Stored Procedure
b) You really want to use a column-list with insert.

regards,

Kristof

Yea, I just know basics of stored procedures, I'd love to hear how this sort of thing is usually done.

How to insert data with dollar ($) first with sqlxml ?

Hi,
I wish to insert the following data into an sql table using sqlxml. All
columns in the table are varchar. The crafted xml is as follow:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync><updg:after>
<temp_Outstanding drawdownApp="LOA"
drawdownId="XYZ"
facilityId="$J5EZEFN">
</temp_Outstanding>
</updg:after></updg:sync>
</ROOT>
But this lamely fail with the following error: Invalid XML elements found
inside sync block (HRESULT="0x80004005").
The code to perform the update is as follow:
SqlXmlCommand cmd = new SqlXmlCommand(conn);
cmd.CommandType = SqlXmlCommandType.UpdateGram;
cmd.CommandText = content;
stm = cmd.ExecuteStream();
The trouble seems to come from the $J5EZEFN field. Is there a way to insert
something into a table that begin with a dollar ($) sign ?
It seems that $XXX is used to pass parameters - but, well, I do not want to
pass parameter. I just want to insert a data with a dollar ($) first.
Is there a way to do this without using stored procedure and altering the
data ?
Thanks,
- Pierre
A brief discussion on handling "invalid XML data" is covered here:
http://msdn.microsoft.com/library/de...egram_375f.asp
Check section:
C. Dealing with valid SQL Server characters that are not valid in XML
- good luck,
Geoff -
"Pierre CHALAMET" wrote:

> Hi,
> I wish to insert the following data into an sql table using sqlxml. All
> columns in the table are varchar. The crafted xml is as follow:
> <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
> <updg:sync><updg:after>
> <temp_Outstanding drawdownApp="LOA"
> drawdownId="XYZ"
> facilityId="$J5EZEFN">
> </temp_Outstanding>
> </updg:after></updg:sync>
> </ROOT>
> But this lamely fail with the following error: Invalid XML elements found
> inside sync block (HRESULT="0x80004005").
> The code to perform the update is as follow:
> SqlXmlCommand cmd = new SqlXmlCommand(conn);
> cmd.CommandType = SqlXmlCommandType.UpdateGram;
> cmd.CommandText = content;
> stm = cmd.ExecuteStream();
>
> The trouble seems to come from the $J5EZEFN field. Is there a way to insert
> something into a table that begin with a dollar ($) sign ?
> It seems that $XXX is used to pass parameters - but, well, I do not want to
> pass parameter. I just want to insert a data with a dollar ($) first.
> Is there a way to do this without using stored procedure and altering the
> data ?
> Thanks,
> - Pierre
>
|||XXX is perfectly valid in xml and even encoding this as &x36; does not solve
any problem. I strongly believe that $XYZ is reserved for parameter.
Is there a way to disable parameters in this kind of xml ? I just want to
know how to espace dollar ($) when used as 1st char in a field.
- Pierre
"Geoff Ely" wrote:
[vbcol=seagreen]
> A brief discussion on handling "invalid XML data" is covered here:
> http://msdn.microsoft.com/library/de...egram_375f.asp
> Check section:
> C. Dealing with valid SQL Server characters that are not valid in XML
> - good luck,
> Geoff -
> "Pierre CHALAMET" wrote:
|||The problem is that when the Updategram does not have an associated
mapping-schema, the Updategram processor treats the "$" sign as a special
currency symbol and expects a numerical value after it.
As far as I know the workaround is to use a mapping schema.
Thank you,
Amar Nalla [MSFT]
PS: This posting is provided "AS IS" and confers on rights or warranties
"Pierre CHALAMET" <PierreCHALAMET@.discussions.microsoft.com> wrote in
message news:B82E01B2-B043-4E32-9048-ADF49C469805@.microsoft.com...[vbcol=seagreen]
> XXX is perfectly valid in xml and even encoding this as &x36; does not
> solve
> any problem. I strongly believe that $XYZ is reserved for parameter.
> Is there a way to disable parameters in this kind of xml ? I just want to
> know how to espace dollar ($) when used as 1st char in a field.
> - Pierre
>
>
> "Geoff Ely" wrote:
|||I think I could not provide a mapping schema. The reason is that I'm using
BizTalk Server 2004 with an SQL send port (which does use sqlxml behind the
scene).
Since I'm having a bug with an orchestration because of a bad placed dollar
I sadly thought this could be solved easily with the understanding of sqlxml
behaviour.
I'll go back to biztalk server newsgroup so...
- Pierre

How to insert data with dollar ($) first with sqlxml ?

Hi,
I wish to insert the following data into an sql table using sqlxml. All
columns in the table are varchar. The crafted xml is as follow:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync><updg:after>
<temp_Outstanding drawdownApp="LOA"
drawdownId="XYZ"
facilityId="$J5EZEFN">
</temp_Outstanding>
</updg:after></updg:sync>
</ROOT>
But this lamely fail with the following error: Invalid XML elements found
inside sync block (HRESULT="0x80004005").
The code to perform the update is as follow:
SqlXmlCommand cmd = new SqlXmlCommand(conn);
cmd.CommandType = SqlXmlCommandType.UpdateGram;
cmd.CommandText = content;
stm = cmd.ExecuteStream();
The trouble seems to come from the $J5EZEFN field. Is there a way to insert
something into a table that begin with a dollar ($) sign ?
It seems that $XXX is used to pass parameters - but, well, I do not want to
pass parameter. I just want to insert a data with a dollar ($) first.
Is there a way to do this without using stored procedure and altering the
data ?
Thanks,
- PierreA brief discussion on handling "invalid XML data" is covered here:
http://msdn.microsoft.com/library/d...
egram_375f.asp
Check section:
C. Dealing with valid SQL Server characters that are not valid in XML
- good luck,
Geoff -
"Pierre CHALAMET" wrote:

> Hi,
> I wish to insert the following data into an sql table using sqlxml. All
> columns in the table are varchar. The crafted xml is as follow:
> <ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
> <updg:sync><updg:after>
> <temp_Outstanding drawdownApp="LOA"
> drawdownId="XYZ"
> facilityId="$J5EZEFN">
> </temp_Outstanding>
> </updg:after></updg:sync>
> </ROOT>
> But this lamely fail with the following error: Invalid XML elements found
> inside sync block (HRESULT="0x80004005").
> The code to perform the update is as follow:
> SqlXmlCommand cmd = new SqlXmlCommand(conn);
> cmd.CommandType = SqlXmlCommandType.UpdateGram;
> cmd.CommandText = content;
> stm = cmd.ExecuteStream();
>
> The trouble seems to come from the $J5EZEFN field. Is there a way to inser
t
> something into a table that begin with a dollar ($) sign ?
> It seems that $XXX is used to pass parameters - but, well, I do not want t
o
> pass parameter. I just want to insert a data with a dollar ($) first.
> Is there a way to do this without using stored procedure and altering the
> data ?
> Thanks,
> - Pierre
>|||XXX is perfectly valid in xml and even encoding this as &x36; does not solve
any problem. I strongly believe that $XYZ is reserved for parameter.
Is there a way to disable parameters in this kind of xml ? I just want to
know how to espace dollar ($) when used as 1st char in a field.
- Pierre
"Geoff Ely" wrote:
> A brief discussion on handling "invalid XML data" is covered here:
> http://msdn.microsoft.com/library/d...tegram_375f.asp
> Check section:
> C. Dealing with valid SQL Server characters that are not valid in XML
> - good luck,
> Geoff -
> "Pierre CHALAMET" wrote:
>|||The problem is that when the Updategram does not have an associated
mapping-schema, the Updategram processor treats the "$" sign as a special
currency symbol and expects a numerical value after it.
As far as I know the workaround is to use a mapping schema.
Thank you,
Amar Nalla [MSFT]
PS: This posting is provided "AS IS" and confers on rights or warranties
"Pierre CHALAMET" <PierreCHALAMET@.discussions.microsoft.com> wrote in
message news:B82E01B2-B043-4E32-9048-ADF49C469805@.microsoft.com...
> XXX is perfectly valid in xml and even encoding this as &x36; does not
> solve
> any problem. I strongly believe that $XYZ is reserved for parameter.
> Is there a way to disable parameters in this kind of xml ? I just want to
> know how to espace dollar ($) when used as 1st char in a field.
> - Pierre
>
>
> "Geoff Ely" wrote:
>|||I think I could not provide a mapping schema. The reason is that I'm using
BizTalk Server 2004 with an SQL send port (which does use sqlxml behind the
scene).
Since I'm having a bug with an orchestration because of a bad placed dollar
I ly thought this could be solved easily with the understanding of sqlxml
behaviour.
I'll go back to biztalk server newsgroup so...
- Pierre

how to index large varchar field

Here's the situation: we have a character based field of up to 2048
characters in length that we want to store in a single column in a
sqlserver table. need to be able to search on this field and guarantee
uniqueness. it would be nice if this field has an index with a unique
constraint. the problem is that sqlserver can't index anything over
900 characters in length.
any suggestions appreciated.
we have a couple thoughts so far:
1 - compress this field in the data access layer before saving in sql -
that should keep the length of the field under 900 chars and let us
apply a unique index in sql. however, we would still have the
uncompressed, unindexed version of the field in another column
searching.
2 - split the field into multiple columns each under 900 characters.
apply an index to each column. however, we'd lose the uniqueness
constraint and this would complicate searches.
3 - combination of 1 and 2.
by the way - we're using SQL Server 2005.
In an effort to maintain uniqueness you could investigate creating three
additional columns (varchar (683)) then popultate the data from the field in
question evenly across the new columns. To ensure uniqueness you could
created a concatenated key with the three new columns. Just a thought....
Thomas
"steve.c.thompson@.gmail.com" wrote:

> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and guarantee
> uniqueness. it would be nice if this field has an index with a unique
> constraint. the problem is that sqlserver can't index anything over
> 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql -
> that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
> 3 - combination of 1 and 2.
> by the way - we're using SQL Server 2005.
>
|||I was thinking the same thing Thomas. I created the three additional
columns and the tried to create the composity key, but sql won't create
an index across the three fields since the sum of the field lengths is
greater then 900.
steve.
|||steve.c.thompson@.gmail.com wrote:
> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and
> guarantee uniqueness. it would be nice if this field has an index
> with a unique constraint. the problem is that sqlserver can't index
> anything over 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql
> - that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
Compression means you're dealing with binary data and you're not going
to get a lot of compression on a max of 2K worth of data.

> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
Adds additional management.
I would recommend that you hash the data and generate a 32-bit hash
value. You can do this from the client using .Net, for example, or using
an extended stored procedure. SQL Server has the T-SQL BINARY_CHECKSUM()
function, but it has some limitations generating the same checksum for a
few different values. However, the T-SQL function would be quite easy to
implement using an INSERT/UPDATE trigger on the table.
select BINARY_CHECKSUM('ABC123'), BINARY_CHECKSUM('aBC123')
|||Steve,
I think you must get the data size down to 900 characters or less,
otherwise you will not be able to guarantee uniqueness.
As for the searching: if you have to search in the texts (for example
LIKE '%some text%'), then an index is only useful if the data column is
narrow in comparison to average row size. That is probably not the case
here, which means a table scan (or clustered index scan) is probably
most efficient.
If it is relatively small in comparison to the average row size, then
you could create a separate table with only a key column and the
varchar(2048) column, with a one-to-one relation to the original table.
HTH,
Gert-Jan
steve.c.thompson@.gmail.com wrote:
> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and guarantee
> uniqueness. it would be nice if this field has an index with a unique
> constraint. the problem is that sqlserver can't index anything over
> 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql -
> that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
> 3 - combination of 1 and 2.
> by the way - we're using SQL Server 2005.
|||Thanks for the replies guys.
We were originally using a hash for the key as David suggested, but
thinking back to my days at university when we had to program hash
algorithms, they dont gaurantee unquiness. In which case the insert
would cause a sql exception and we wouldn't be able to store certain
data strings with non-unique hashes. That would be bad.
In the end, I think we're just going to have to reduce the field to 900
characters as Gert-Jan has suggested. I don't think there's any other
way around it.
steve
|||why didnt u use the text datatype..any reason
|||i need to be able to put a unique index on this field, text fields do
not allow that.

how to index large varchar field

Here's the situation: we have a character based field of up to 2048
characters in length that we want to store in a single column in a
sqlserver table. need to be able to search on this field and guarantee
uniqueness. it would be nice if this field has an index with a unique
constraint. the problem is that sqlserver can't index anything over
900 characters in length.
any suggestions appreciated.
we have a couple thoughts so far:
1 - compress this field in the data access layer before saving in sql -
that should keep the length of the field under 900 chars and let us
apply a unique index in sql. however, we would still have the
uncompressed, unindexed version of the field in another column
searching.
2 - split the field into multiple columns each under 900 characters.
apply an index to each column. however, we'd lose the uniqueness
constraint and this would complicate searches.
3 - combination of 1 and 2.
by the way - we're using SQL Server 2005.In an effort to maintain uniqueness you could investigate creating three
additional columns (varchar (683)) then popultate the data from the field in
question evenly across the new columns. To ensure uniqueness you could
created a concatenated key with the three new columns. Just a thought....
--
Thomas
"steve.c.thompson@.gmail.com" wrote:

> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and guarantee
> uniqueness. it would be nice if this field has an index with a unique
> constraint. the problem is that sqlserver can't index anything over
> 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql -
> that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
> 3 - combination of 1 and 2.
> by the way - we're using SQL Server 2005.
>|||I was thinking the same thing Thomas. I created the three additional
columns and the tried to create the composity key, but sql won't create
an index across the three fields since the sum of the field lengths is
greater then 900.
steve.|||steve.c.thompson@.gmail.com wrote:
> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and
> guarantee uniqueness. it would be nice if this field has an index
> with a unique constraint. the problem is that sqlserver can't index
> anything over 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql
> - that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
Compression means you're dealing with binary data and you're not going
to get a lot of compression on a max of 2K worth of data.

> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
Adds additional management.
I would recommend that you hash the data and generate a 32-bit hash
value. You can do this from the client using .Net, for example, or using
an extended stored procedure. SQL Server has the T-SQL BINARY_CHECKSUM()
function, but it has some limitations generating the same checksum for a
few different values. However, the T-SQL function would be quite easy to
implement using an INSERT/UPDATE trigger on the table.
select BINARY_CHECKSUM('ABC123'), BINARY_CHECKSUM('aBC123')|||Steve,
I think you must get the data size down to 900 characters or less,
otherwise you will not be able to guarantee uniqueness.
As for the searching: if you have to search in the texts (for example
LIKE '%some text%'), then an index is only useful if the data column is
narrow in comparison to average row size. That is probably not the case
here, which means a table scan (or clustered index scan) is probably
most efficient.
If it is relatively small in comparison to the average row size, then
you could create a separate table with only a key column and the
varchar(2048) column, with a one-to-one relation to the original table.
HTH,
Gert-Jan
steve.c.thompson@.gmail.com wrote:
> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and guarantee
> uniqueness. it would be nice if this field has an index with a unique
> constraint. the problem is that sqlserver can't index anything over
> 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql -
> that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
> 3 - combination of 1 and 2.
> by the way - we're using SQL Server 2005.|||Thanks for the replies guys.
We were originally using a hash for the key as David suggested, but
thinking back to my days at university when we had to program hash
algorithms, they dont gaurantee unquiness. In which case the insert
would cause a sql exception and we wouldn't be able to store certain
data strings with non-unique hashes. That would be bad.
In the end, I think we're just going to have to reduce the field to 900
characters as Gert-Jan has suggested. I don't think there's any other
way around it.
steve|||why didnt u use the text datatype..any reason|||i need to be able to put a unique index on this field, text fields do
not allow that.sql

how to index large varchar field

Here's the situation: we have a character based field of up to 2048
characters in length that we want to store in a single column in a
sqlserver table. need to be able to search on this field and guarantee
uniqueness. it would be nice if this field has an index with a unique
constraint. the problem is that sqlserver can't index anything over
900 characters in length.
any suggestions appreciated.
we have a couple thoughts so far:
1 - compress this field in the data access layer before saving in sql -
that should keep the length of the field under 900 chars and let us
apply a unique index in sql. however, we would still have the
uncompressed, unindexed version of the field in another column
searching.
2 - split the field into multiple columns each under 900 characters.
apply an index to each column. however, we'd lose the uniqueness
constraint and this would complicate searches.
3 - combination of 1 and 2.
by the way - we're using SQL Server 2005.In an effort to maintain uniqueness you could investigate creating three
additional columns (varchar (683)) then popultate the data from the field in
question evenly across the new columns. To ensure uniqueness you could
created a concatenated key with the three new columns. Just a thought....
--
Thomas
"steve.c.thompson@.gmail.com" wrote:
> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and guarantee
> uniqueness. it would be nice if this field has an index with a unique
> constraint. the problem is that sqlserver can't index anything over
> 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql -
> that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
> 3 - combination of 1 and 2.
> by the way - we're using SQL Server 2005.
>|||I was thinking the same thing Thomas. I created the three additional
columns and the tried to create the composity key, but sql won't create
an index across the three fields since the sum of the field lengths is
greater then 900.
steve.|||steve.c.thompson@.gmail.com wrote:
> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and
> guarantee uniqueness. it would be nice if this field has an index
> with a unique constraint. the problem is that sqlserver can't index
> anything over 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql
> - that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
Compression means you're dealing with binary data and you're not going
to get a lot of compression on a max of 2K worth of data.
> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
Adds additional management.
I would recommend that you hash the data and generate a 32-bit hash
value. You can do this from the client using .Net, for example, or using
an extended stored procedure. SQL Server has the T-SQL BINARY_CHECKSUM()
function, but it has some limitations generating the same checksum for a
few different values. However, the T-SQL function would be quite easy to
implement using an INSERT/UPDATE trigger on the table.
select BINARY_CHECKSUM('ABC123'), BINARY_CHECKSUM('aBC123')|||Steve,
I think you must get the data size down to 900 characters or less,
otherwise you will not be able to guarantee uniqueness.
As for the searching: if you have to search in the texts (for example
LIKE '%some text%'), then an index is only useful if the data column is
narrow in comparison to average row size. That is probably not the case
here, which means a table scan (or clustered index scan) is probably
most efficient.
If it is relatively small in comparison to the average row size, then
you could create a separate table with only a key column and the
varchar(2048) column, with a one-to-one relation to the original table.
HTH,
Gert-Jan
steve.c.thompson@.gmail.com wrote:
> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and guarantee
> uniqueness. it would be nice if this field has an index with a unique
> constraint. the problem is that sqlserver can't index anything over
> 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql -
> that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
> 3 - combination of 1 and 2.
> by the way - we're using SQL Server 2005.|||Thanks for the replies guys.
We were originally using a hash for the key as David suggested, but
thinking back to my days at university when we had to program hash
algorithms, they dont gaurantee unquiness. In which case the insert
would cause a sql exception and we wouldn't be able to store certain
data strings with non-unique hashes. That would be bad.
In the end, I think we're just going to have to reduce the field to 900
characters as Gert-Jan has suggested. I don't think there's any other
way around it.
steve|||why didnt u use the text datatype..any reason|||i need to be able to put a unique index on this field, text fields do
not allow that.

How to increment a column with varchar data type

hi,
is there any method for incrementing the varchar field
i have an emp table with columns
create table emprecord(eno varchar(10), ename varchar(20))
i cant give eno column with identity field because identity column must
be of data type int, bigint, smallint, tinyint, or decimal or numeric
insert into emprecord values ('MH1001','satish')
insert into emprecord values ('MH1002','rehman')
my problem is there any method --when i pass only the ename the eno
field should be automatically incremented as MH1003 --each time it has
to check for the highest eno ie.., MH1002 is my highest eno in the
table emprecord
i think there is sequence option in MS Sql server-- if so how to use
sequence
pls help me
thanks
satishTry a search about "SQL Server identity values", but it supports integer
values only. You might need to make appropriate modifications on your table.
Martin C K Poon
Senior Analyst Programmer
====================================
"satish" <satishkumar.gourabathina@.gmail.com> ?
news:1145000321.252703.52230@.t31g2000cwb.googlegroups.com ?...
> hi,
> is there any method for incrementing the varchar field
> i have an emp table with columns
> create table emprecord(eno varchar(10), ename varchar(20))
> i cant give eno column with identity field because identity column must
> be of data type int, bigint, smallint, tinyint, or decimal or numeric
> insert into emprecord values ('MH1001','satish')
> insert into emprecord values ('MH1002','rehman')
> my problem is there any method --when i pass only the ename the eno
> field should be automatically incremented as MH1003 --each time it has
> to check for the highest eno ie.., MH1002 is my highest eno in the
> table emprecord
> i think there is sequence option in MS Sql server-- if so how to use
> sequence
>
> pls help me
> thanks
> satish
>|||You could use a computed column instead|||satish
create table #t
(
rowid int not null identity(1,1) primary key,
empno AS 'MH'+CAST(rowid AS VARCHAR(10)),
empname VARCHAR (50)
)
insert into #t(empname) VALUES ('Smith')
insert into #t (empname)VALUES ('Clinton')
insert into #t (empname) VALUES ('Brown')
select empno,empname from #t
drop table #t
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1145000321.252703.52230@.t31g2000cwb.googlegroups.com...
> hi,
> is there any method for incrementing the varchar field
> i have an emp table with columns
> create table emprecord(eno varchar(10), ename varchar(20))
> i cant give eno column with identity field because identity column must
> be of data type int, bigint, smallint, tinyint, or decimal or numeric
> insert into emprecord values ('MH1001','satish')
> insert into emprecord values ('MH1002','rehman')
> my problem is there any method --when i pass only the ename the eno
> field should be automatically incremented as MH1003 --each time it has
> to check for the highest eno ie.., MH1002 is my highest eno in the
> table emprecord
> i think there is sequence option in MS Sql server-- if so how to use
> sequence
>
> pls help me
> thanks
> satish
>|||there are no identity columns defined in my table in that case how to
do
this is my table
create table emprecord(eno varchar(10), ename varchar(20))
i always pass only ename|||It's better to break empno into empPrefix (varchar(2)) and empSerial (int).
or, if the table structure cannot be modified, I will opt to use stored
procedure (and/or other programming means).
create table #MyTempEmpRecord06041402
(
empno varchar(10),
empname VARCHAR (50)
)
go
-- Assumes that @.myEmpnoPrefix is always having 2 characters
create procedure #MyInsertEmpname06041402
@.myEmpnoPrefix varchar(2),
@.myEmpname varchar(50)
as
insert #MyTempEmpRecord06041402
select @.myEmpnoPrefix
+ (select convert(varchar(8), convert(int, isnull(max(substring(empno,
1 + len(@.myEmpnoPrefix), 8)), '0')) + 1) as MySerial
from #MyTempEmpRecord06041402
where left(empno, len(@.myEmpnoPrefix)) = @.myEmpnoPrefix)
as empno,
@.myEmpname as empname
go
exec #MyInsertEmpname06041402 'MH', 'Smith'
exec #MyInsertEmpname06041402 'MH', 'Clinton'
exec #MyInsertEmpname06041402 'MP', 'Smith'
exec #MyInsertEmpname06041402 'MP', 'Brown'
exec #MyInsertEmpname06041402 'MH', 'Brown'
go
select * from #MyTempEmpRecord06041402
go
drop procedure #MyInsertEmpname06041402
go
drop table #MyTempEmpRecord06041402
Martin C K Poon
Senior Analyst Programmer
====================================
"satish" <satishkumar.gourabathina@.gmail.com> ?
news:1145007066.106515.192790@.t31g2000cwb.googlegroups.com ?...
> there are no identity columns defined in my table in that case how to
> do
> this is my table
> create table emprecord(eno varchar(10), ename varchar(20))
> i always pass only ename
>

how to increase size of datatype...

Dear

I am using varchar data type in my table.it provides maximum 8000 character. but i want more than that.

how to increase size more than 8000

i want to use text datatype but when i type in length it doenst type.it displays only 16.i cant change to another value.

please help me out

Waiting for reply

Regards,
ASIFTEXT does not take a length the way VARCHAR does

it's just TEXT

How to include parameter in WHERE statment of a stored procedure?

I have a stored procedure like:

PROCEDURE Procedure_ABC
@.Id VARCHAR(8000),
@.LocationId VARCHAR(8000)
AS
BEGIN
SELECT table.id
FROM table
WHERE table.id = @.Id
AND table.LocationId in (SELECT id FROM tempTable)

END

My problem is, the locationId will be null or length equals 0 some time, how can I make the statement "table.LocationId in (SELECT id FROM tempTable)" included into the WHERE condition dynamically depends on locationId is null or length equals 0?

Is that possible to do it at Database side? Or I have to do it at code side?

Thank you.

Perhaps something like this:

SELECT t.id

FROM Table t

WHERE ( t.id = @.Id
AND ( t.LocationID in (SELECT ID FROM TempTable)

OR t.LocationID IS NULL

)

)
END

|||

Looks like you want dynamic search capabilities based on the parameters passed. You can take a look at the link below for various options:

http://www.sommarskog.se/dyn-search.html

You can do below in your case:

SELECT table.id
FROM table
WHERE table.id = @.Id
AND table.LocationId in (SELECT id FROM tempTable) and @.LocationId is not null and len(@.LocationId) > 0

UNION ALL

SELECT table.id
FROM table
WHERE table.id = @.Id
AND table.LocationId in (SELECT id FROM tempTable) and (@.LocationId is null or len(@.LocationId) = 0)

Query optimizer will use startup expression filter to evaluate the expression containing @.LocationId at run-time. This will result in only one of the UNION ALL branches being executed. You could use this approach. You can also put this query in a inline TVF and use it.

|||

Thank you for your advise.

For your code, my understanding is you list both situations, either parameter has value or not has value, run both and combine the results. Is that correct?

My question is, if @.LocationId is not null, the condition "(@.LocationId is null or len(@.LocationId) = 0)" will be false, and it will AND with condition "table.LocationId in (SELECT id FROM tempTable)", the result will also be false right? Finally, it will AND with "table.id = @.Id", which will be false again. That will make the result set of

SELECT table.id
FROM table
WHERE table.id = @.Id
AND table.LocationId in (SELECT id FROM tempTable) and (@.LocationId is null or len(@.LocationId) = 0)

be null?

Cause I have about 8-9 parameters need to be passed in, follow your code, I guess the stored procedure will be very long, right?

The link you gave provides a coding way to do the if statement to build the query at database.

Thank you.

|||Try the following:

Code Snippet

PROCEDURE Procedure_ABC
@.Id VARCHAR(8000),
@.LocationId VARCHAR(8000),
@.Parameter2 INT,
@.Parameter3 NUMERIC(18, 2)
AS
SELECT
table.id
FROM
table
WHERE
table.id = @.Id AND
(table.LocationId in (SELECT id FROM tempTable) OR LEN(ISNULL(@.LocationId, '')) = 0) AND
(table.Value2 = @.Parameter2 OR @.Parameter2 IS NULL) AND
(table.Value3 = @.Parameter3 OR @.Parameter3 IS NULL) etc...

If you're checking VARCHARs, NVARCHARs, CHARs, to see if they're either NULL or have length = 0, put an ISNULL around the parameter and compare the length to 0.

For every other parameter you want to check, compare it to the table value and OR it with a check to see if it's null.

Each of those OR'd NULL checks needs to be in brackets with AND clauses otherwise you'll get strange results.

How to include a single quote in a sql query

Hi

Declare @.Customer varchar(255)
Set @.Customer = Single quotes + customer name + single quotes

Select Customerid from Customer Where name = @.Customer

I have a query written above, but i was not able to add single quotes to the set statement above. Can i know as how to go about it?

Early reply is much appreciated.

Thanks!

You can not write?

set @.Customer = 'customer name'

What is the problem with writing that? Do you get an error?

|||

Nopes, here iam using a variable called "customer name" to which values will be passed in dynamically,

so the query is like

set @.Customer = single quotes + customer name(variable) + single quotes

Hope it is clear, or else if you need more information let me know.

Thanks!

|||

try this....

'''' + Customer Name + ''''

If you want to give the Single Quote on String Litteral you need to use 2 Single Quote Continuously..

-mani

|||set @.Customer = '''' + CustomerName + ''''|||

hey...

char(39) is the ascii for single quotes...

declare @.sql varchar(110)

set @.sql = 'select char(39)+name1+char(39) from test_name'

exec (@.gg)

--assuming test_name has 2 records mak and robin , so the output is

'MAK'

'ROBIN'

|||

edukulla wrote:

Nopes, here iam using a variable called "customer name" to which values will be passed in dynamically,

so the query is like

set @.Customer = single quotes + customer name(variable) + single quotes

Still not clear, a few more questions unless the other replies helped you.

What kind of variable is customer name?
How do you want to execute the SQL statements?
If you are doing this in a programming language, what programming language?

|||

Hello,

If your issue is that you are having difficulties finding a way to deal with character string which may contain one or more single quotes, then the solution is NOT to surround the string with single quotes as a previous user suggested. This will only work if there is in fact onle one single quote in your string such as O'Brian. It will not work if there are multiple quotes such as Here's O'Brian.

SET QUOTED_IDENTIFIER OFF

DECLARE @.s VARCHAR(100)

SET @.s = " Here's O'Brian and some quotes: ''''''''' "

PRINT @.s

Cheers,

Rob