Can I INSERT INTO a temp tbl twice in one stored procedure

In my stored procedure I need to select some data columns and insert them into a #temp tbl and then select the same data columns again using a different from and put them into the same #temp tbl. It sounds like a union, can I union into a #temp tbl?

Any help here is appreciated.

You should be able to insert into the temp table twice, or use a union to just do one insert. Have you actually tried and got an error? If so can you post the error you got.|||The error in my stored procedure is:

There is already an object named '#temp_UN' in the database.

I'm trying to do something like this:

SELECT ClinetID, etc... INTO #temp_UN FROM ... WHERE...

SELECT ClinetID, etc... INTO #temp_UN FROM ... WHERE... (This select has a different FROM

SELECT ... FROM tblClient INNER JOIN tblClient.ClientID = #temp_UN.ClientID WHERE...

if

object_id('tempdb..#temp_UN','U')isnotnull

drop

table #temp_UNGifts|||

For your second insert into the temp table you should be able to use the 'Insert into .. select .. ' syntax

Something like this:

SELECT ClinetID, etc... INTO #temp_UN FROM ... WHERE...

INSERT INTO #temp_UN SELECT ClinetID, etc...FROM ... WHERE... (This select has a different FROM)

SELECT ... FROM tblClient INNER JOIN tblClient.ClientID = #temp_UN.ClientID WHERE...

HTH

|||

Thank you Steve, it looks like it was the order of things.

can i get the output of 'sp_columns' system SP into a table through T-SQL

Hi,
I want to collect the output of system stored procedure 'sp_columns' into a temporary table. Please suggest how can i achieve this.

Ashu Sharma

Create a temp table with columns as described at sp_columns in BOL and then do
INSERT INTO your_temp_table EXEC sp_columns
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de|||Thanks Franksql

Can I get both Attributes and Elements values in same OpenXML query ?

As an input parameter for stored procedure I have the following XML, its values are contained in both attributes and elements. For attributes we have to use flag = 1, and for attributes 2. Is there any flag or technique that we can retrieve both ?

Here is my script:

declare
@.xml xml,
@.handle int

set @.xml =
'
<Sortable>
<Field ord="1" type="asc">LastName</Field>
<Field ord="2" type="desc">CreateDate</Field>
<Field ord="3" type="asc">ProspectNum</Field>
</Sortable>
'

exec sp_xml_preparedocument @.handle output, @.xml

select
ord,
type,
Field
from openxml(@.handle, '/Sortable/Field', 1)
with
(
ord int,
type varchar(4),
Field varchar(20) --'./Field'
)

exec sp_xml_removedocument @.handle

I exepect it to return the foillowing result set:

ord type Field
-- - --
1 asc LastName
2 desc CreateDate
3 asc ProspectNum

Obviously it returns NULL for my 'Field' column.

Thanks

Either of these

select
ord,
type,
Field
from openxml(@.handle, '/Sortable/Field', 1)
with
(
ord int,
type varchar(4),
Field varchar(20) '.'
)

select r.value('@.ord','int') as ord,
r.value('@.type','varchar(4)') as type,
r.value('.','varchar(20)') as Field
from @.xml.nodes('/Sortable/Field') as D(r)

Can I generate Table Script in a Stored Procedure?

I have to load around 40 tables and I want to write one Stored Procedure that I pass a table name into. Data is coming from 3 different sources and to fill each of the 40 tables. I want to create a temp table that I can load first to make sure it succeeds first before I truncate and insert into the production table.

Is there a system procedure or something that will allow me to create a temp table from a table that alresdy exist in my database?

You can use SELECT INTO to create a new table based on a query like:

SELECT t.col1, t.col2, t.col3

INTO #t

FROM your_table AS t

Above query will create table with same structure as your_table without the defaults, constraints, indexes etc. Identity property on columns will be transferred though. You may also want to look at SSIS to automate your bulk loading process. You can also take a look at the link below for more pointers on how to improve the bulk load process:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx

Note that with above query, you can create permanent table too. If you create a temporary table then you can only use BULK INSERT statement since the temporary table is connection scoped. And I am not sure if SSIS can perform SELECT...INTO and bulk load from the client side within context of single connection (You will have to ask in the SSIS forum).

|||

Good to know you can create Temps like this.

I might be able to work with this let me know what you think.

Problem:

I’m getting my data from an AS400 using

Insert Into @.TableName

Select *

from Opendatasource(“Connection Info”).AS400ServerName.AS400Library.@.TableName

The table name and column structure in the AS400 are the same as in my database but the column type is different i.e. A lot of the AS400 columns are Char when I need them to be Decimal. I need to create the temp table with the same structure as my table because the majority of the time my process fails is because the AS400 has Char data when it should be Decimal

My Solution:

Select Top 1 *

Into #t

From @.TableName

Truncate Table #t

--Insert temp data from sites

Insert Into #t

Select *

from Opendatasource(“Connection Info”).AS400ServerName.California.@.TableName

Insert Into #t

Select *

from Opendatasource(“Connection Info”).AS400ServerName.Nevada.@.TableName

Insert Into #t

Select *

from Opendatasource(“Connection Info”).AS400ServerName.Arizona.@.TableName

Truncate Table @.TableName

--Insert data to Production

Insert Into @.TableName

Select *

From #t

Can I fill a cursor from a strored procedure?

Basically, I have a complex stored procedure that combines two tables and fills a cursor.

I would like to fill another cursor in another stored procedure from the results of this first stored proc, rather than have to type it all in again.

The reason being that I am doing a one time import of some data from two tables into one new table based on some complex linking/querying.

Can I fill a cursor from the output of another stored procedure rather than an inline SELECT statement?

Does the sp I am using have to have cursor as an out parameter?a more easier way would be to create a table with all the columns tht your first cursor returns...so when you combine the 2 tables and fill the cursor...insert those records into the table..now you have a table with the records...so you can use a select statement on the table itself in the second stored proc...

HTH|||I would rather use the table option. But since the tables are shared between stored procedures then you need to use the local temp table or global temp tables.

But there are options to pass a cursor back to the calling procedure ... You would need to use an output cursor parameter in your stored procedure. Note that cursor parameters can be only output & use the keyword VARYING since the resultset supported is created dynamically by the stored procedure and whose contents can vary. Something along the lines of

CREATE PROCEDURE usp_cursor
@.orders_cursor CURSOR VARYING OUTPUT
AS
SET @.orders_cursor = CURSOR FOR
SELECT *
FROM Northwind..Orders
WHERE Freight > 100.00
OPEN @.titles_cursor
GO

You can now execute this stored procedure in a batch which declares a local cursor and assign the output cursor parameter value to the local cursor variable like :

DECLARE @.local_crsr CURSOR
EXEC usp_cursor @.orders_cursor = @.local_crsr OUTPUT
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @.local_crsr
...
END
CLOSE @.local_crsr
DEALLOCATE @.local_crsr|||hi
i dont have any idea about the topic cursor,can you give me a certain link that it can help me to understand very well this topic and thank you for your help|||Article on cursorssql

Can i execute stored procedure from enterprise manager

Can i execute stored procedure from enterprise manager?
ThanksI would of initially said no... but where there's a will there's a way.

you could always right click on a table, show top whatever rows, and show the SQL Query, in there try typing the "exec procname" statement. That should work. But if i were you, I'd stick with Query Analyzer.|||For what purpose?

Can I encrypt a field of a table using stored procedure?

Can I encrypt a field of a table using stored procedure in SQL Server 2005 Express Edition? And also, how to decrypt it through stored procedure?

Using the encryption functions.

In Books Online, look up the topic: Encryption, sub-topic: Functions.

You can download a version of Books Online for SQLServer Express from:

SQL Server 2005 Express Books Online Express Edition
http://msdn2.microsoft.com/en-us/library/ms165706.aspx

Can I Debug Remote SQL Server 2000 Stored Procedure in Visual Studio 2005

I can debug an SQL Server 2005 stored procedure in Visual Studio 2005 from a local database (e.g. AdventureWorks).

However, when I try to debug a stored procedure on a remote server running SQL Server 2000 the debug process is not accessible (i.e. I can choose 'Step Into Stored Procedure' and it seems to run but I cannot debug it).

Am I trying to do something that just can't be done? Is this a compatibility issue between Visual Studio 2005 and SQL Server 2000? Or am I missing something?

Thanks for any advice on this.

Richard

you can do this by using the server explorer (ctrl+alt+s)

in the server explorer right clcik the object in the server explorer

then click modify

|||

As far as I can see, Server Explorer in Visual Studio does not give me a 'Modify' option. It gives me a 'Step into Stored Procedure' option that does not seem work for remote SQL Server 2000 stored procedures.

The 'Modify' option is available in SQL Server Management Studio Object Explorer but provides no debugging.

Maybe I have something set up wrong?

|||

As far as I can see, Server Explorer in Visual Studio does not give me a 'Modify' option. It gives me a 'Step into Stored Procedure' option that does not seem work for remote SQL Server 2000 stored procedures.

The 'Modify' option is available in SQL Server Management Studio Object Explorer but provides no debugging.

Maybe I have something set up wrong?

|||

the modify option is available on the server explorer if you are

creating an ASP.net/web project.

if you are developing windows application project

use the open option instead for tables its

open table definition

|||

As far as I can see, Server Explorer in Visual Studio does not give me a 'Modify' option. It gives me a 'Step into Stored Procedure' option that does not seem work for remote SQL Server 2000 stored procedures.

The 'Modify' option is available in SQL Server Management Studio Object Explorer but provides no debugging.

Maybe I have something set up wrong?

|||

right click the SP. then click on OPEN on vs2005 server explorer.

can I create a VIEW on the sesult of stored procedure ?

I have a store procedure which select/calculate fields from multiple tables,

can I create a VIEW on the sesult of the stored procedure ?

u can create a view using a select statement containing ur sp converted to a function....
but why would u want to do such a thing....do u want to permanently keep these values..//refer them just then...some more info may get u a better suggestion buddy...|||I want create a cube fact that is base on the result of a stored procedure, but the cube cannot accept SP as a source, so I want to create a view that can be the source of cube fact|||

In this case I think your best bet would probably be to simply create and populate a table with the results of your stored procedure. If you don't need to keep these results then you can truncate and/or drop the table when you don't need it anymore.

I don't think it's possible to create a view based on the output of a SP.

sql

Can I create a Top n statement within a stored procedure using a parameter?

In a 'Top n' type statement I wish to be able to insert the n value
from a parameter, within a stored precedure eg

Having declared @.pageSize as a parameter I want to run the following
type of query :

SELECT DISTINCT TOP @.pageSize routeID, routeName FROM
tblRoute_Header

When I attempt to do so I get an error mesage indicating incorrect
syntax. I do not get an error message if I specify 'n' directly as in
TOP 10

Am I missing something or is this not possible within a stored
procedure?

Best wishes, John MorganOn Mon, 12 Apr 2004 16:45:26 +0100, John Morgan wrote:

>
>In a 'Top n' type statement I wish to be able to insert the n value
>from a parameter, within a stored precedure eg
>Having declared @.pageSize as a parameter I want to run the following
>type of query :
>SELECT DISTINCT TOP @.pageSize routeID, routeName FROM
>tblRoute_Header
>When I attempt to do so I get an error mesage indicating incorrect
>syntax. I do not get an error message if I specify 'n' directly as in
>TOP 10
>Am I missing something or is this not possible within a stored
>procedure?
>Best wishes, John Morgan

The TOP clause will only take an integer value, not a variable.

There are two other ways to limit your output to @.pageSize rows:

1. Using proprietary syntax, not portable to other DBMS's

SET ROWCOUNT @.pageSize
SELECT DISTINCT routeID, routeName
FROM tblRoute_Header
WHERE ...
ORDER BY ...
SET ROWCOUNT 0

Note 1: Don't forget to SET ROWCOUNT 0 afterwards, or else all other
queries you execute will be limited to @.pageSize rows of output.
Note 2: Don't leave out the order by clause, or else your output will
be unpredictable. Result sets, like tables, are unordered by default.
If you get the first 10 from an unordered collection, there's no way
of predicting which 10 it will be, nor can anybody guarantee that
you'll get the same 10 if you get "the first 10" again.

2. Using ANSI-standard syntax:

SELECT DISTINCT routeID, routeName
FROM tblRoute_Header AS RH1
WHERE ...
AND (SELECT COUNT(*)
FROM tblRoute_Header AS RH2
WHERE RH2.routeID < RH1.routeID) < @.pageSize
ORDER BY routeID

Note 1: This is based on assumptions re your data structure. You need
to adapt it to your actual situation.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||"John Morgan" <jfm@.XXwoodlander.co.uk> wrote in message
news:ntdl70h6hmja4h5rshiiuob1hcg32pm12d@.4ax.com...
>
> In a 'Top n' type statement I wish to be able to insert the n value
> from a parameter, within a stored precedure eg
> Having declared @.pageSize as a parameter I want to run the following
> type of query :
> SELECT DISTINCT TOP @.pageSize routeID, routeName FROM
> tblRoute_Header
> When I attempt to do so I get an error mesage indicating incorrect
> syntax. I do not get an error message if I specify 'n' directly as in
> TOP 10
> Am I missing something or is this not possible within a stored
> procedure?
> Best wishes, John Morgan

TOP doesn't allow parameters, but SET ROWCOUNT does:

SET ROWCOUNT @.n

SELECT ...

SET ROWCOUNT 0

Don't forget to set ROWCOUNT back to zero immediately after your query, or
all the following statements will be affected too. Note that TOP without
ORDER BY, as in your example above, returns random rows - there is no
guarantee that you will get what you expect without the ORDER BY.

Simon|||"John Morgan" <jfm@.XXwoodlander.co.uk> wrote in message
news:ntdl70h6hmja4h5rshiiuob1hcg32pm12d@.4ax.com...
>
> In a 'Top n' type statement I wish to be able to insert the n value
> from a parameter, within a stored precedure eg
> Having declared @.pageSize as a parameter I want to run the following
> type of query :
> SELECT DISTINCT TOP @.pageSize routeID, routeName FROM
> tblRoute_Header
> When I attempt to do so I get an error mesage indicating incorrect
> syntax. I do not get an error message if I specify 'n' directly as in
> TOP 10
> Am I missing something or is this not possible within a stored
> procedure?
> Best wishes, John Morgan

TOP doesn't allow parameters, but SET ROWCOUNT does:

SET ROWCOUNT @.n

SELECT ...

SET ROWCOUNT 0

Don't forget to set ROWCOUNT back to zero immediately after your query, or
all the following statements will be affected too. Note that TOP without
ORDER BY, as in your example above, returns random rows - there is no
guarantee that you will get what you expect without the ORDER BY.

Simon|||Thank you Simon for your help - appreciated,

Best wishes, John Morgan

On Mon, 12 Apr 2004 22:06:52 +0200, "Simon Hayes" <sql@.hayes.ch>
wrote:

>"John Morgan" <jfm@.XXwoodlander.co.uk> wrote in message
>news:ntdl70h6hmja4h5rshiiuob1hcg32pm12d@.4ax.com...
>>
>>
>> In a 'Top n' type statement I wish to be able to insert the n value
>> from a parameter, within a stored precedure eg
>>
>> Having declared @.pageSize as a parameter I want to run the following
>> type of query :
>>
>> SELECT DISTINCT TOP @.pageSize routeID, routeName FROM
>> tblRoute_Header
>>
>> When I attempt to do so I get an error mesage indicating incorrect
>> syntax. I do not get an error message if I specify 'n' directly as in
>> TOP 10
>>
>> Am I missing something or is this not possible within a stored
>> procedure?
>>
>> Best wishes, John Morgan
>TOP doesn't allow parameters, but SET ROWCOUNT does:
>SET ROWCOUNT @.n
>SELECT ...
>SET ROWCOUNT 0
>Don't forget to set ROWCOUNT back to zero immediately after your query, or
>all the following statements will be affected too. Note that TOP without
>ORDER BY, as in your example above, returns random rows - there is no
>guarantee that you will get what you expect without the ORDER BY.
>Simon|||Thank you Simon for your help - appreciated,

Best wishes, John Morgan

On Mon, 12 Apr 2004 22:06:52 +0200, "Simon Hayes" <sql@.hayes.ch>
wrote:

>"John Morgan" <jfm@.XXwoodlander.co.uk> wrote in message
>news:ntdl70h6hmja4h5rshiiuob1hcg32pm12d@.4ax.com...
>>
>>
>> In a 'Top n' type statement I wish to be able to insert the n value
>> from a parameter, within a stored precedure eg
>>
>> Having declared @.pageSize as a parameter I want to run the following
>> type of query :
>>
>> SELECT DISTINCT TOP @.pageSize routeID, routeName FROM
>> tblRoute_Header
>>
>> When I attempt to do so I get an error mesage indicating incorrect
>> syntax. I do not get an error message if I specify 'n' directly as in
>> TOP 10
>>
>> Am I missing something or is this not possible within a stored
>> procedure?
>>
>> Best wishes, John Morgan
>TOP doesn't allow parameters, but SET ROWCOUNT does:
>SET ROWCOUNT @.n
>SELECT ...
>SET ROWCOUNT 0
>Don't forget to set ROWCOUNT back to zero immediately after your query, or
>all the following statements will be affected too. Note that TOP without
>ORDER BY, as in your example above, returns random rows - there is no
>guarantee that you will get what you expect without the ORDER BY.
>Simon

can I create a job to call a stored procedure?

Hello,

Can I create a job to call a stored procedure everyday? If yes, how to do it?

Thanks

I use sql server 2005.

Thanks

|||

Yes, refer to Books Online, Topic: SQL Agent, Jobs

Unless you are using SQL Express. With SQL Express, SQL Agent is not included. You can accomplish the same functionality using the Windows Scheduler service, SQLCmd.exe and a stored procedure.

|||

from sql agen job, I can't find stored procedure calling item from the drop down list. It only has maintanence plan etc.

Thanks

|||If you will explore Books Online, Topic: Jobs, Creating, you will find excellent walk-thoughs and examples.|||You have
1) create new job,
2) add a step to created job
3) Add name for this step, such as "Step1"
4) Choose type: Transact-SQL script
5) Choose actual database from list
6) Add command:

Code Snippet

EXEC sp_yourstored_proc_name

7) Go to schedules tab and set correct schedule plan|||

Easiest way to at least for me is to use isql wrapped in a bat file.

Open notepad or your favorite text editor.

Type the following, replacing where required.

@.echo off
isql -S SERVER_NAME_OR_IP -d DATABASE_NAME -Q "EXEC STORED_PROC_NAME" -U YOUR_USERNAME -P YOUR_PASSWORD -s , -o "OUTPUT_FILE"

Save the file with .bat as the extension.

Navigate to Start, All Programs, Control Panel, Scheduled Tasks, Add Scheduled Task.

Select next from the introduction screen.

Select browse from the next screen and find the .bat file created earlier.

Give the job a name and select interval from the next screen.

Enter the username and password to run as (helpful if using integrated security).

Select finish.

You can test your job by navigating to the scheduled jobs folder, right click on the job, select run

You can obtain the command arguments for isql using "isql /?" (w/o the quotes) at the command prompt.

Simple bat example (will execute sp_monitor and log the results to c:\sqlout.csv)

@.echo off

isql -S 192.168.1.250 -d master -Q "EXEC sp_monitor" -U test -P test -s , -o "c:\sqlout.csv"

Can I configure a SQLDataSource object to use.....

.. the Membership ProviderUserKey value as the parameter of a stored procedure ?

It seems obvious to me that you would want to do this, but I can only see that it allows you to pass Profile parameters.

You can pass any value as a parameter. It has some built-in parameter types, such as ProfileParameter, FormParameter, ControlParameter etc, but you can just use <asp:Parameter>. Beyond the most basic of usage, the SqlDataSource isn't the most intuitive of controls, I'm afraid. This should help you unpick it a bit though:http://aspnet.4guysfromrolla.com/articles/030106-1.aspx

|||You can always write your own parameter:

1using System;2using System.ComponentModel;3using System.Globalization;4using System.Web;5using System.Web.Security;6using System.Web.UI;7using System.Web.UI.WebControls;89public class MembershipParameter : Parameter10{11public MembershipParameter(string name, TypeCode typeCode,string propertyName) :base(name, typeCode)12 {13this.PropertyName = propertyName;14 }1516public MembershipParameter(string name,string propertyName) :base(name)17 {18this.PropertyName = propertyName;19 }2021public MembershipParameter()22 {23 }2425protected MembershipParameter(MembershipParameter original) :base(original)26 {27if (null == original)throw new ArgumentNullException("original");28this.PropertyName = original.PropertyName;29 }3031public string PropertyName32 {33get34 {35object value = ViewState["PropertyName"];36if (null !=value)return (string)value;37return string.Empty;38 }39set40 {41if (null !=value && 0 ==value.Length)value =null;42if (!string.Equals(value, (string)ViewState["PropertyName"], StringComparison.OrdinalIgnoreCase))43 {44 ViewState["PropertyName"] =value;45 OnParameterChanged();46 }47 }48 }4950protected override Parameter Clone()51 {52return new MembershipParameter(this);53 }5455protected override object Evaluate(HttpContext context, Control control)56 {57object result =null;58string prop =this.PropertyName;59if (!string.IsNullOrEmpty(prop))60 {61 MembershipUser user = Membership.GetUser();62if (null != user)63 {64 result = DataBinder.Eval(user, prop);65 }66 }6768return result;69 }70}

Can i call Tigger in store procedure or vice versa and How?

Can i call Tigger in store procedure or vice versa in sql server 2000.

and How it implement ?

Your question has been replied in anthor post, please check it:

http://forums.asp.net/1318011/ShowPost.aspx

To call stored procedure in trigger, just call it like anywhere else you call it:

EXEC sp_mysp

sql

Can I call stored procedure inside the case statement

Hi
Can I call stored procedure inside the case statement.small correction
Can I execute stored procedure inside the case statement
CASE t1.OPERATION WHEN 'I' THEN (EXEC [UPD_SEQ_GENERATOR_PROC_VEERU]
@.p_seq_name, @.p_next_value1 OUTPUT select @.p_next_value1 WHEN 'L' THEN EXEC
[UPD_SEQ_GENERATOR_PROC_VEERU] @.p_seq_name, @.p_next_value1 OUTPUT select
@.p_next_value1 END
I want to return the value from the stroed procedure based on the condition.
Regards
Veeru
"Veeru" wrote:

> Hi
> Can I call stored procedure inside the case statement.
>|||No. Why don't you use IF ?
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:4BCD8E67-FB16-46BF-9298-4242A70D05E2@.microsoft.com...
> small correction
> Can I execute stored procedure inside the case statement
>
> CASE t1.OPERATION WHEN 'I' THEN (EXEC [UPD_SEQ_GENERATOR_PROC_VEERU]
> @.p_seq_name, @.p_next_value1 OUTPUT select @.p_next_value1 WHEN 'L' THEN
> EXEC
> [UPD_SEQ_GENERATOR_PROC_VEERU] @.p_seq_name, @.p_next_value1 OUTPUT select
> @.p_next_value1 END
> I want to return the value from the stroed procedure based on the
> condition.
> Regards
> Veeru
>
>
> "Veeru" wrote:
>|||Hi veeru,
I have been following your posts for quite sometime. Can you give the
exact requirement, why you ned that sequence table and what are you trying t
o
achieve with an example and may be we can help you out.
-Omnibuzz
"Veeru" wrote:
> small correction
> Can I execute stored procedure inside the case statement
>
> CASE t1.OPERATION WHEN 'I' THEN (EXEC [UPD_SEQ_GENERATOR_PROC_VEERU]
> @.p_seq_name, @.p_next_value1 OUTPUT select @.p_next_value1 WHEN 'L' THEN EXE
C
> [UPD_SEQ_GENERATOR_PROC_VEERU] @.p_seq_name, @.p_next_value1 OUTPUT select
> @.p_next_value1 END
> I want to return the value from the stroed procedure based on the conditio
n.
> Regards
> Veeru
>
>
> "Veeru" wrote:
>|||Hi Omnibuzz,
I have the requirement to load the flat file data into tables. previously we
did this in Orale. there we created one control file to load the flat file
data into table. In the control file we did some validations like
DATA_STREAMER_ID "DATA_STREAMER_ID_SEQ.nextval",
KEY_2 CONSTANT 'PRES',
KEY_3 CONSTANT '-1',
KEY_4 "Data_Streamer_Id_Seq.Nextval",
OPERATION " DECODE(TRIM(:OPERATION),'L',1,'I',1,'U',
0)",
IDENTIFIER_15 "DECODE
(TRIM:OPERATION),'L',SEQ_IS_GLOBAL_IDENT
IFIER.nextval,'I',SEQ_IS_GLOBAL_IDEN
TIFIER.nextval)"
DATE_1 DATE "FXYYYY-MM-DD HH24:MI:SS" "DECODE(:OPERATION,'L',NVL
(:DATE_1,to_char(sysdate,'YYYY-MM-DD
HH24:MI:SS')),'I',NVL(:DATE_1,to_char(sy
sdate,'YYYY-MM-DD
HH24:MI:SS')),:DATE_1)", -- STATUS_CHANGE_DATE
I want to do the same thing now in SQL server. For that I have created
Format file to map the data file fields to table column.
we have CASE statement in SQL Server which works same as DECODE in Oracle.
I have posted some more info yesterday subject as "Can we update the table
in user defined function". Actually I have been waiting for your reply for
that. Can you please refer that also and suggest me the approach.
Regards
Veeru.
"Omnibuzz" wrote:
> Hi veeru,
> I have been following your posts for quite sometime. Can you give the
> exact requirement, why you ned that sequence table and what are you trying
to
> achieve with an example and may be we can help you out.
> -Omnibuzz
> "Veeru" wrote:
>|||Do you want the values to be autogenerated like this?
KEY_1 KEY_4
1 1
2 2
3 3
or like this
KEY_1 KEY_4
1 2
3 4
5 6|||I want values like
KEY_1 KEY_4
1 2
3 4
5 6
and
I want the below syntax in INSERT...OPENROWSER(BULK) in SQL Server
IDENTIFIER_15 "DECODE
(TRIM:OPERATION),'L',SEQ_IS_GLOBAL_IDENT
IFIER.nextval,'I',SEQ_IS_GLOBAL_IDEN
TIFIER.nextval)"
Regards
Veeru
"Omnibuzz" wrote:

> Do you want the values to be autogenerated like this?
> KEY_1 KEY_4
> 1 1
> 2 2
> 3 3
> or like this
> KEY_1 KEY_4
> 1 2
> 3 4
> 5 6
>|||then use a table variable. Insert into the table variable from the source.
And select from the table variable and insert into the destination.
The code is this...
--The table variable definition should be something like this.
declare @.tbl1 table (id1 int identity(1,2), source_col1,source_col2,...)
Insert into @.tbl1 (source_col1,source_col2,... ) select ... from source.
--Here id1 will be autogenerated into the table variable.
--use this for insert into destination
INSERT...OPENROWSER(BULK) select id1, source_col1...., case when blah..blah
then id1 + 1 else
... blah blah.. end
Sorry.. leaving for a party :)
Try to decipher this.. If not will help u (in detail :)
Bye.|||Thanks Omnibuzz. I will try. If any problem I will post my doubts
"Omnibuzz" wrote:

> then use a table variable. Insert into the table variable from the source.
> And select from the table variable and insert into the destination.
> The code is this...
> --The table variable definition should be something like this.
> declare @.tbl1 table (id1 int identity(1,2), source_col1,source_col2,...)
> Insert into @.tbl1 (source_col1,source_col2,... ) select ... from source.
> --Here id1 will be autogenerated into the table variable.
> --use this for insert into destination
> INSERT...OPENROWSER(BULK) select id1, source_col1...., case when blah..bl
ah
> then id1 + 1 else
> ... blah blah.. end
>
> Sorry.. leaving for a party :)
> Try to decipher this.. If not will help u (in detail :)
> Bye.
>|||Hi Omnibuzz,
I have multiple records in my flat file. I have to load all the records into
the database. Like this I have to load into so many tables and have one
interface fir each. the sequence number should be unique in the database not
in the table. So I have to generate sequence number for all the Interfaces
where evevr I need and should be unique. In Oracle we can create one sequenc
e
and generate the sequences. Like that I have to do. I think you can
understand what I need.
Can you suggest me.
Thanks in advance.
Regards
Veeru
"Omnibuzz" wrote:

> then use a table variable. Insert into the table variable from the source.
> And select from the table variable and insert into the destination.
> The code is this...
> --The table variable definition should be something like this.
> declare @.tbl1 table (id1 int identity(1,2), source_col1,source_col2,...)
> Insert into @.tbl1 (source_col1,source_col2,... ) select ... from source.
> --Here id1 will be autogenerated into the table variable.
> --use this for insert into destination
> INSERT...OPENROWSER(BULK) select id1, source_col1...., case when blah..bl
ah
> then id1 + 1 else
> ... blah blah.. end
>
> Sorry.. leaving for a party :)
> Try to decipher this.. If not will help u (in detail :)
> Bye.
>

Can I build WHERE clause dynamically in a Stored Procedure in SQL Server 2005?

Hello,
I am using SQL Server 2005 and I am just wondering if I can build the
WHERE clause dynamically. I only want to build WHERE class if the my
@.VIN count is of exactly 17 charecters. Here is my code
****************************************
*********
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SP_SearchGroundedVehicles]
@.vin varchar(17)
AS
BEGIN
SET NOCOUNT ON;
SELECT V.vin
,C.FirstName
,C.LastName
,C.AccountNum
,D.DealerShipName
,Contrt.MaturityDate
FROM ContractInfo Contrt
Inner Join VehicleInformation V ON Contrt.VehicleID = V.VehicleID
Inner Join DealerShips D ON Contrt.DealerShipID = D.DealerShipID
Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
if count(@.vin) = 17
begin
Where VIN = @.VIn
end
END
****************************************
***************
But when I parse the above stored proc its throwing this message
********
Msg 156, Level 15, State 1, Procedure SP_SearchGroundedVehicles, Line
33
Incorrect syntax near the keyword 'Where'.
********
If it is possible to dynamically build WHERE clause please correct the
way I am doing it or if it is not possible what is the best way of
writing it?
Thanks
-LYou could do something like this:
SELECT V.vin
,C.FirstName
,C.LastName
,C.AccountNum
,D.DealerShipName
,Contrt.MaturityDate
FROM ContractInfo Contrt
Inner Join VehicleInformation V ON Contrt.VehicleID = V.VehicleID
Inner Join DealerShips D ON Contrt.DealerShipID = D.DealerShipID
Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
where case when len(@.Vin) = 17 then @.Vin else VIN end = VIN|||Wow this is popular today.
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
"Learner" <pradev@.gmail.com> wrote in message
news:1142368232.024389.309720@.z34g2000cwc.googlegroups.com...
> Hello,
> I am using SQL Server 2005 and I am just wondering if I can build the
> WHERE clause dynamically. I only want to build WHERE class if the my
> @.VIN count is of exactly 17 charecters. Here is my code
> ****************************************
*********
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
> ALTER PROCEDURE [dbo].[SP_SearchGroundedVehicles]
> @.vin varchar(17)
> AS
> BEGIN
> SET NOCOUNT ON;
> SELECT V.vin
> ,C.FirstName
> ,C.LastName
> ,C.AccountNum
> ,D.DealerShipName
> ,Contrt.MaturityDate
> FROM ContractInfo Contrt
> Inner Join VehicleInformation V ON Contrt.VehicleID = V.VehicleID
> Inner Join DealerShips D ON Contrt.DealerShipID = D.DealerShipID
> Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
> if count(@.vin) = 17
> begin
> Where VIN = @.VIn
> end
> END
> ****************************************
***************
> But when I parse the above stored proc its throwing this message
>
> ********
> Msg 156, Level 15, State 1, Procedure SP_SearchGroundedVehicles, Line
> 33
> Incorrect syntax near the keyword 'Where'.
> ********
>
> If it is possible to dynamically build WHERE clause please correct the
> way I am doing it or if it is not possible what is the best way of
> writing it?
> Thanks
> -L
>|||> where case when len(@.Vin) = 17 then @.Vin else VIN end = VIN
At first this struck me as incorrect, but technically it's not, just not in
the format I'd typically expect, e.g. compare the readability to:
WHERE VIN = CASE LEN(@.vin) WHEN 17 THEN @.Vin ELSE VIN END|||Hello,
Thank you all for the above suggestions and WHERE VIN = CASE LEN(@.vin)
WHEN 17 THEN @.Vin ELSE VIN END works.
I am not sure if we build this in SQL Server 2000 but SQL Server 2005
rocks!
Thanks
-L|||Question "du jour". :-)
Would you like a nice bottle of Merlot with that?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ueXCca6RGHA.1236@.TK2MSFTNGP11.phx.gbl...
> Wow this is popular today.
> http://www.sommarskog.se/dyn-search.html
> http://www.sommarskog.se/dynamic_sql.html
>
>
> "Learner" <pradev@.gmail.com> wrote in message
> news:1142368232.024389.309720@.z34g2000cwc.googlegroups.com...
>|||Well why not :) By the way what do you mean by "du jour". ?
Thanks
-L|||Well why not :) By the way what do you mean by "du jour". ?
Thanks
-L|||"Learner" <pradev@.gmail.com> wrote in message
news:1142376483.643521.89440@.v46g2000cwv.googlegroups.com...
> Well why not :) By the way what do you mean by "du jour". ?
> Thanks
> -L
It's French.
http://en.wikipedia.org/wiki/Soup_du_jour|||>> I only want to build WHERE class if the my @.VIN count is of exactly 17 ch
arecters.<<
This is crazy. A VIN is CHAR(17) by definition; read your ISO
standard. Furthermroe, it has a very fixed format for each position in
the string. What you want is a CHECK() constraint or procedure that
will validate your input
NOTES ON VIN:
In North America, a system is used that is far more stringent than the
ISO Standards but is "backward compatible." Here, the VIN is divided
into four sections:
The first three characters shall uniquely identify the manufacturer,
make and type of vehicle (with the same exception of manufacturers that
produce less than 500 vehicles). Effectively, this is the WMI. There
are indeed examples of manufacturers who have more than one WMI that
use the third character as a code for a vehicle category (for instance
bus or truck). Just as often however this is not the case;
The second section consists of five characters (VIN positions 4-8) and
identifies the attributes of the vehicle. For each type of vehicle
(passenger cars, MPV's, trucks, buses, trailers, motorcycles,
incomplete vehicles other than trailers), different information is
required. For cars, MPV's and light trucks it is required that the
first two characters of this section are alphabetic, the third and
fourth shall be numeric and the fifth alphanumeric. This section is the
VDS in ISO 3779 but there it comprises another position of the VIN;
The third section consists of one character which is the check digit,
calculated over the other 16 characters of the VIN. This character can
be numeric or the letter X;
The fourth section consists of eight characters on positions 10-17 of
the VIN. The last five shall be numeric for cars, MPV's and light
trucks and the last four shall be numeric for all other vehicles. The
first character represents the vehicle model year, the second character
represents the plant of manufacture. The third through eighth
characters are a sequential production number (for manufacturers
producing more than 500 vehicles per year). For other manufacturers,
the sixth, seventh and eight positions represent the sequential
production number.
This section confirms to the VIS in ISO 3779.
A portion of the VIN is the WMI (World Manufacturer Identifier) Code.
SAE assigns this code to U.S. vehicle manufacturers. If you are a U.S.
manufacturer, please contact:
Cathy Douds
WMI Coordinator
SAE International
400 Commonwealth Drive
Warrendale, PA 15096-0001
724.772.8511
724.776.4026 - fax
douds@.sae.org
Related Standards:
There are several standards available on VINs and WMIs:
SAE - J187 - Truck Vehicle Identification Numbers
SAE - J218 - Passenger Car Identification Terminology
SAE - J272 - Vehicle Identification Number Systems
SAE - J273 - Passenger Car Vehicle Identification Number System
SAE - J853 - Vehicle Identification Numbers
SAE - J1108 - Truck and Truck Tractor Vehicle Identification Number
Systems
SAE - J1044 - World Manufacturer Identifier
SAE - J1229 - Truck Identification Terminology
SAE - J1877 - Recommended Practice for Bar-Coded Vehicle Identification
Number Label
SAE J129 - Engine and Transmission Identification Numbers
ISO 3779 - Road vehicles - Vehicle identification number (VIN) Content
and structure
ISO 3780 - Road vehicles - World manufacturer identifier (WMI) code

Can I avoid temp tables, etc.

I need help on two questions:
1. Is temp table the only way to pass recordsets from a nested stored
procedure to a calling stored procedure? Can we avoid temp tables in
this case?
2. Are operations in a stored procedure are treated as a transaction?

Any help will be greatly appreciated.

Background: We need to use temp table to pass recordsets from a nested
stored procedure to a calling stored procedure. Our understanding is
that in this case, we have no choice but to use temp tables. So, we
need to optimize the performance as much as possible. To do this, we
wanted to find out whether operations in a stored procedure are treated
as a transaction. We are using SQL 2000 SP4. I could not find any
answers so I did the following experiment.

Experiment 1:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[Wiz_SP_Transaction_Test]
GO

CREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]
AS

Update
Articles
SET
IsUpdate = 20
where
ArticlesId < 80000

SELECT * from Articles

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

"SELECT * from Articles" takes a long time (about 40 seconds) to
complete

Before executing the SP, the IsUpdate attribute for all articles is 30.
Then I executed this SP. Before the SP is finished, I end the SP
manually. I checked the IsUpdate attribute again, and found that all
Articles's (ArticlesId < 80000) Isupdate attribute is now 20. The
operations did not rollback. I interpret this to mean that the whole SP
is not treated as a transaction.

Then, I did experiment 2 below. This time, I explicitly declared the
transaction.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[Wiz_SP_Transaction_Test]
GO

CREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]
AS
BEGIN TRANSACTION
Update
Articles
SET
IsUpdate = 50
where
ArticlesId < 80000

SELECT * from Articles

IF @.@.ERROR <0 ROLLBACK TRANSACTION
COMMIT TRANSACTION
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Before this second SP, the IsUpdate attribute is 20 (set in the first
experiment). I run this second SP and ended it manually before it
finished. I checked the IsUpdate attributes for all Articles's
(ArticlesId < 80000), but their Isupdate attribute is 50. So the
operation did not rollback either. But we have declared the transaction
explicitly. Does this mean that the SP is still not treated as a
transaction?(betbubble@.gmail.com) writes:

Quote:

Originally Posted by

I need help on two questions:
1. Is temp table the only way to pass recordsets from a nested stored
procedure to a calling stored procedure? Can we avoid temp tables in
this case?

No, there are more alternative: use a process-keyed table. As long
as the access is from T-SQL only, @.@.spid works fine. We use this
technique a lot in our shop.

There is also INSERT-EXEC, but I like this less.

I discuss these options in more detail in an article on my web site:
http://www.sommarskog.se/share_data.html

Quote:

Originally Posted by

2. Are operations in a stored procedure are treated as a transaction?

A procedure as such does not define any transaction scope. However,
each INSERT, UPDATE and DELETE statement defines a transaction if
there is no other transaction active. This transaction includs any
trigger that is fired the statement. And in case of INSERT EXEC, the
called procedure will operate in the context of the transaction
defined by the INSERT statement.

Note that this applies, regardless of the INSERT, UPDATE or DELETE
statement appears in a stored procedure or not.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||What are you using SET ANSI_NULLS OFF for?|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:

Quote:

Originally Posted by

What are you using SET ANSI_NULLS OFF for?

I would guess that betbubble uses Enterprise Manager to create his
procedures. Which is a very bad idea, for the precise reason Alexander
points out (thanks for catching it!): Enterprise Manager has incorrect
defaults for ANSI_NULLS and QUTOED_IDENTIFIERS. You have rarely reason
to have these options off (least of all ANSI_NULLS), but there are
features in SQL Server that requires these settings to be ON, so by
all means run with them.

If you use Query Analyzer to edit stored procedures, you get the
correct defaults.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland and Alexander,
Thanks a lot. I will experiment with them and report back.|||These SPs were generated using the Query Analyzer, ANSI_NULLS and
QUTOED_IDENTIFIERS are OFF. I turned them ON manually. Is there
something wrong with my Query Analyzer settings?

I read the article by Erland. Great information! Thanks. I am
experimenting with the Process-Keyed tables, which are very big tables.
I have many querys concurrentlly, they will need to use the same
Process-Keyed tables. Any advice on reducing locks will be appreciated.|||(betbubble@.gmail.com) writes:

Quote:

Originally Posted by

These SPs were generated using the Query Analyzer, ANSI_NULLS and
QUTOED_IDENTIFIERS are OFF. I turned them ON manually. Is there
something wrong with my Query Analyzer settings?

You can change the connection settings under Tools->Options->Cononection
Properties. The default settings is that all settings for indexed views
are on, but you might have changed that at some point.

Also, if the SP was originally created by EM, and you scripted it from
QA, QA will include the original settings in the script, so you will
actively have to change them - or just remove them-

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

can i add a column to an article in transactionalreplication

hi
how can i add a column to the table at some specified location after publishing the article in transactional replication.is there any procedure or method.please explain
thanks®ards
reddy
Reddy,
sp_repladdcolumn is the standard way to add a column to replication. However
this will not permit you to place the column at a specific position in the
table. This is not normally an issue, as TSQL is not usually written in a
way that makes it order dependant - columns are referred to by name and not
by position. If this is a requirement for you, you'll need to drop the
publication, add the column then recreate the publication.
HTH,
Paul Ibison
|||paul,
thank you.
in transactional replication on subscriber side we have to set autoid property to no.now i am setting this manually.
we have script to set the autoid 'not for replication',like that any script you have to set the autoid property NO for all tables in a replication.please help.
thanks®ards
reddy
|||Reddy,
I haven't used this myself, but Hilary has posted up a
reference to a script he uses:
http://communities2.microsoft.com/co.../newsgroups/en
-us/default.aspx?
dg=microsoft.public.sqlserver.replication&mid=2239 a0b8-
d572-4d86-ac92-5109662bef6b
This updates to Yes - Not for Replication. However, you
could modify this script for your use. You'll still need
to alter the script for each table though.
Also I'd like to know a little more about why the
subscriber columns were originally set to have an identity
attribute and now it's being removed - (it's normally the
opposite :-)).
Regards,
Paul Ibison
|||paul,
first i will explain the way how i am doing transactional replication.first i will change the autoid column of publisher to not for replication.then i will restore the same in subscriber and set the auto id property to no.why means in transactional publis
her it self handles the autoid property so no need in subscriber.
is there any mistake in the above process please reply
thanks & regards
reddy
|||Reddy,
this is correct for a nosync initialization for standard
transactional replication (RO subscribers).
Regards,
Paul Ibison
|||paul
thanks
regards
reddy

Can I achieve WITH(NOLOCK) on all joins in a stored procedure with a single command?

I have a number of reporting stored procedures that purely list
records and make no changes to the data. I have noticed that some of
these SPs are causing blocks so I am adding the WITH(NOLOCK) hint. For
a simple example :-
Select * from table1 WITH(NOLOCK)
INNER JOIN table2 WITH(NOLOCK) ON table1.UID=table2.UID
INNER JOIN table3 WITH(NOLOCK) ON table1.AnotherID=table3.AnotherID
LEFT OUTER JOIN table4 WITH(NOLOCK) ON table3.ThisID=table4.ThisID
There lots of these and many of them have lots of joins so I'm looking
for a way to apply WITH(NOLOCK) to the whole procedure and save myself
the time it takes to add the hint to each table/join. I know that it
is possible to use SET DEADLOCK_PRIORITY LOW, forcing the procedure to
volunteer as the deadlock victim, but this isn't suitable as I need
the procedure to return it's records.
Does anybody have a suggestion or am I looking ata couple of days of
ctrl-v'ing WITH(NOLOCK) everywhere?
Thanks,
LiamHow about below?
SET STRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Liam Weston" <liam_weston@.hotmail.com> wrote in message
news:5f9a8c3b.0310310227.436b158b@.posting.google.com...
> I have a number of reporting stored procedures that purely list
> records and make no changes to the data. I have noticed that some of
> these SPs are causing blocks so I am adding the WITH(NOLOCK) hint. For
> a simple example :-
> Select * from table1 WITH(NOLOCK)
> INNER JOIN table2 WITH(NOLOCK) ON table1.UID=table2.UID
> INNER JOIN table3 WITH(NOLOCK) ON table1.AnotherID=table3.AnotherID
> LEFT OUTER JOIN table4 WITH(NOLOCK) ON table3.ThisID=table4.ThisID
> There lots of these and many of them have lots of joins so I'm looking
> for a way to apply WITH(NOLOCK) to the whole procedure and save myself
> the time it takes to add the hint to each table/join. I know that it
> is possible to use SET DEADLOCK_PRIORITY LOW, forcing the procedure to
> volunteer as the deadlock victim, but this isn't suitable as I need
> the procedure to return it's records.
> Does anybody have a suggestion or am I looking ata couple of days of
> ctrl-v'ing WITH(NOLOCK) everywhere?
> Thanks,
> Liam|||Thanks, that's just what I was looking for.
Liam
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in message news:<uvCEm35nDHA.2312@.TK2MSFTNGP12.phx.gbl>...
> How about below?
> SET STRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Liam Weston" <liam_weston@.hotmail.com> wrote in message
> news:5f9a8c3b.0310310227.436b158b@.posting.google.com...
> > I have a number of reporting stored procedures that purely list
> > records and make no changes to the data. I have noticed that some of
> > these SPs are causing blocks so I am adding the WITH(NOLOCK) hint. For
> > a simple example :-
> >
> > Select * from table1 WITH(NOLOCK)
> > INNER JOIN table2 WITH(NOLOCK) ON table1.UID=table2.UID
> > INNER JOIN table3 WITH(NOLOCK) ON table1.AnotherID=table3.AnotherID
> > LEFT OUTER JOIN table4 WITH(NOLOCK) ON table3.ThisID=table4.ThisID
> >
> > There lots of these and many of them have lots of joins so I'm looking
> > for a way to apply WITH(NOLOCK) to the whole procedure and save myself
> > the time it takes to add the hint to each table/join. I know that it
> > is possible to use SET DEADLOCK_PRIORITY LOW, forcing the procedure to
> > volunteer as the deadlock victim, but this isn't suitable as I need
> > the procedure to return it's records.
> >
> > Does anybody have a suggestion or am I looking ata couple of days of
> > ctrl-v'ing WITH(NOLOCK) everywhere?
> >
> > Thanks,
> >
> > Liam

Can anyone help me in this?

I want to write a function or procedure that deletes any characters
other than aplhabets.
At the same time i want to update a cloumn in the same table(say column
changecode with value 1 if my function deleted any of the characters)
I did however wrote a function to delete BUT.... I am not able to
update the table column 'changecode' .
Can anyone help me in this'Are you getting an error message ?
Could you post what you've done.
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"sridhar" <sridharkola17@.gmail.com> wrote in message
news:1146812711.153743.134530@.i39g2000cwa.googlegroups.com...
> I want to write a function or procedure that deletes any characters
> other than aplhabets.
> At the same time i want to update a cloumn in the same table(say column
> changecode with value 1 if my function deleted any of the characters)
> I did however wrote a function to delete BUT.... I am not able to
> update the table column 'changecode' .
> Can anyone help me in this'
>|||Hello,
You would wand to create the function first, then use it in a sql statement
to update the table. For example, the following function would convert the
string:
--
CREATE FUNCTION FnAlphaString
(@.string varchar(100))
RETURNS VARCHAR(100)
as
BEGIN
DECLARE @.position int, @.newString varchar(100)
-- Initialize the current position and the string variables.
SET @.position = 1
SET @.NewString = ''
WHILE @.position <= DATALENGTH(@.string)
BEGIN
IF ( ASCII(SUBSTRING(@.string, @.position, 1)) < 65 OR
ASCII(SUBSTRING(@.string, @.position, 1)) > 122)
OR ( ASCII(SUBSTRING(@.string, @.position, 1)) > 90 AND
ASCII(SUBSTRING(@.string, @.position, 1)) < 97)
BEGIN
SET @.newString = @.newString
END
ELSE
BEGIN
SET @.newString = @.newString + SUBSTRING(@.string, @.position, 1)
END
SET @.position = @.position + 1
END
RETURN @.newString
END
---
Then update the table you needed:
Example:
Create TABLE TestString (strRname VARCHAR(100),
Changed bit)
INSERT INTO TestString VALUES('This is ^54 now&*~` a 8[] string',0)
INSERT INTO TestString VALUES('Thisisalreadyacleanstring',0)
update TestString set strRname = dbo.FnAlphaString(strRname), changed = 1
WHERE len(strRname) > len( dbo.FnAlphaString(strRname))
Thanks Kllyj64
"sridhar" wrote:
> I want to write a function or procedure that deletes any characters
> other than aplhabets.
> At the same time i want to update a cloumn in the same table(say column
> changecode with value 1 if my function deleted any of the characters)
> I did however wrote a function to delete BUT.... I am not able to
> update the table column 'changecode' .
> Can anyone help me in this'
>

Can anyone help me in this?

I want to write a function or procedure that deletes any characters
other than aplhabets.
At the same time i want to update a cloumn in the same table(say column
changecode with value 1 if my function deleted any of the characters)
I did however wrote a function to delete BUT.... I am not able to
update the table column 'changecode' .
Can anyone help me in this'Are you getting an error message ?
Could you post what you've done.
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"sridhar" <sridharkola17@.gmail.com> wrote in message
news:1146812711.153743.134530@.i39g2000cwa.googlegroups.com...
> I want to write a function or procedure that deletes any characters
> other than aplhabets.
> At the same time i want to update a cloumn in the same table(say column
> changecode with value 1 if my function deleted any of the characters)
> I did however wrote a function to delete BUT.... I am not able to
> update the table column 'changecode' .
> Can anyone help me in this'
>|||Hello,
You would wand to create the function first, then use it in a sql statement
to update the table. For example, the following function would convert the
string:
--
CREATE FUNCTION FnAlphaString
(@.string varchar(100))
RETURNS VARCHAR(100)
as
BEGIN
DECLARE @.position int, @.newString varchar(100)
-- Initialize the current position and the string variables.
SET @.position = 1
SET @.NewString = ''
WHILE @.position <= DATALENGTH(@.string)
BEGIN
IF ( ASCII(SUBSTRING(@.string, @.position, 1)) < 65 OR
ASCII(SUBSTRING(@.string, @.position, 1)) > 122)
OR ( ASCII(SUBSTRING(@.string, @.position, 1)) > 90 AND
ASCII(SUBSTRING(@.string, @.position, 1)) < 97)
BEGIN
SET @.newString = @.newString
END
ELSE
BEGIN
SET @.newString = @.newString + SUBSTRING(@.string, @.position, 1)
END
SET @.position = @.position + 1
END
RETURN @.newString
END
---
Then update the table you needed:
Example:
Create TABLE TestString (strRname VARCHAR(100),
Changed bit)
INSERT INTO TestString VALUES('This is ^54 now&*~` a 8[] string',0)
INSERT INTO TestString VALUES('Thisisalreadyacleanstring',0)
update TestString set strRname = dbo.FnAlphaString(strRname), changed = 1
WHERE len(strRname) > len( dbo.FnAlphaString(strRname))
Thanks Kllyj64
"sridhar" wrote:

> I want to write a function or procedure that deletes any characters
> other than aplhabets.
> At the same time i want to update a cloumn in the same table(say column
> changecode with value 1 if my function deleted any of the characters)
> I did however wrote a function to delete BUT.... I am not able to
> update the table column 'changecode' .
> Can anyone help me in this'
>