abt Stored Procedure ...

hello guys ..

today i came with a Common question...

i am developing a website in that, I am using Stored procedure for all queries ( select, insert, update, delete )

is it good to run all queries in stored procedure ... or we should run only the SELECT queries by SP ... i need ur valuable suggestion ...

cheers ...i am waiting for some experienced person's answer ... is anybody here ??|||

Quote:

Originally Posted by rameshcse2005

i am waiting for some experienced person's answer ... is anybody here ??

hi,
You can use any sql statement inside your stored procedure...

According to me if you use stored procedures in your applications it will be good, if you need any database change just manipulate your sp, no need of rebuild or recompile your application.. all you need is organizing your application and stored procedures. better the design you have better the result you get|||You can use all the DML statments inside a procedure.|||Thank you very much for ur valuable suggestion ... :-)

and i have another doubt ?

how to pass multiple value in same parameter in SQL stored procedure ??

for example i have to write stored procedure for this query

DELETE FROM tableName where ID in (id1,id2,id3) ?|||You need to pass a refcursor to the procedure to dynamically pass any number of parameters for the IN operator..|||Thanks for ur suggestion ... i w'l try & let u know if any doubts ... :-)|||

Quote:

Originally Posted by rameshcse2005

Thanks for ur suggestion ... i w'l try & let u know if any doubts ... :-)

hi
you can do this as follows

send the id as a string seperated by ,(comma)

in your stored procedure use the following query

DELETE FROM tableName where ID in
( select id from tableName
where (','+isnull(@.parameter,convert(varchar(10),Id) )+',') like
('%,'+convert(varchar(20),Id)+ ',%')
)

In the above query if you pass null value to the @.Parameter then all the records will be deleted, if you send the id values seperated by coma then specific records only will be deleted

abt RTRIM/LTRIM ,Null values

Hi
can any one solve my problem; i could not get solution for following
query
I have two tables like tblOrg and tblorgchartreport
so i need to update tblorg for newly inserted rows with
tblorgchartreport, suppose there are firstname, lastname
so i need to update these fields in tblorg by triming two fields like
i need to get firstname + lastname from tblorgchartreport into
firstname of tblorg. so i have few null values in lastname fields .
so can any one tell me how to solve avoid null values while using RTRIM
or LTRIM when inserting values
waiting for reply thank you in advance
*** Sent via Developersdex http://www.codecomments.com ***
Hi
You can use this way:
SELECT LTRIM(RTRIM(ISNULL(FirstName,''))) + LTRIM(RTRIM(ISNULL(LastName,'')))
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"krishna nellutla" wrote:

> Hi
> can any one solve my problem; i could not get solution for following
> query
> I have two tables like tblOrg and tblorgchartreport
> so i need to update tblorg for newly inserted rows with
> tblorgchartreport, suppose there are firstname, lastname
> so i need to update these fields in tblorg by triming two fields like
> i need to get firstname + lastname from tblorgchartreport into
> firstname of tblorg. so i have few null values in lastname fields .
> so can any one tell me how to solve avoid null values while using RTRIM
> or LTRIM when inserting values
> waiting for reply thank you in advance
>
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||Does this help?
use tempdb
go
create table names (f varchar(30), l varchar(30))
go
insert names (f,l) values ('krishna','nellutla')
insert names (f,l) values ('mark','allison')
insert names (f,l) values ('ben',null)
set concat_null_yields_null off
select rtrim(l) + ', ' + ltrim(f) from names
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
krishna nellutla wrote:
> Hi
> can any one solve my problem; i could not get solution for following
> query
> I have two tables like tblOrg and tblorgchartreport
> so i need to update tblorg for newly inserted rows with
> tblorgchartreport, suppose there are firstname, lastname
> so i need to update these fields in tblorg by triming two fields like
> i need to get firstname + lastname from tblorgchartreport into
> firstname of tblorg. so i have few null values in lastname fields .
> so can any one tell me how to solve avoid null values while using RTRIM
> or LTRIM when inserting values
> waiting for reply thank you in advance
>
> *** Sent via Developersdex http://www.codecomments.com ***

abt RTRIM/LTRIM ,Null values

Hi
can any one solve my problem; i could not get solution for following
query
I have two tables like tblOrg and tblorgchartreport
so i need to update tblorg for newly inserted rows with
tblorgchartreport, suppose there are firstname, lastname
so i need to update these fields in tblorg by triming two fields like
i need to get firstname + lastname from tblorgchartreport into
firstname of tblorg. so i have few null values in lastname fields .
so can any one tell me how to solve avoid null values while using RTRIM
or LTRIM when inserting values
waiting for reply thank you in advance
*** Sent via Developersdex http://www.developersdex.com ***Hi
You can use this way:
SELECT LTRIM(RTRIM(ISNULL(FirstName,''))) + LTRIM(RTRIM(ISNULL(LastName,'')))
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"krishna nellutla" wrote:
> Hi
> can any one solve my problem; i could not get solution for following
> query
> I have two tables like tblOrg and tblorgchartreport
> so i need to update tblorg for newly inserted rows with
> tblorgchartreport, suppose there are firstname, lastname
> so i need to update these fields in tblorg by triming two fields like
> i need to get firstname + lastname from tblorgchartreport into
> firstname of tblorg. so i have few null values in lastname fields .
> so can any one tell me how to solve avoid null values while using RTRIM
> or LTRIM when inserting values
> waiting for reply thank you in advance
>
> *** Sent via Developersdex http://www.developersdex.com ***
>|||Does this help?
use tempdb
go
create table names (f varchar(30), l varchar(30))
go
insert names (f,l) values ('krishna','nellutla')
insert names (f,l) values ('mark','allison')
insert names (f,l) values ('ben',null)
set concat_null_yields_null off
select rtrim(l) + ', ' + ltrim(f) from names
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
krishna nellutla wrote:
> Hi
> can any one solve my problem; i could not get solution for following
> query
> I have two tables like tblOrg and tblorgchartreport
> so i need to update tblorg for newly inserted rows with
> tblorgchartreport, suppose there are firstname, lastname
> so i need to update these fields in tblorg by triming two fields like
> i need to get firstname + lastname from tblorgchartreport into
> firstname of tblorg. so i have few null values in lastname fields .
> so can any one tell me how to solve avoid null values while using RTRIM
> or LTRIM when inserting values
> waiting for reply thank you in advance
>
> *** Sent via Developersdex http://www.developersdex.com ***

abt RTRIM/LTRIM ,Null values

Hi
can any one solve my problem; i could not get solution for following
query
I have two tables like tblOrg and tblorgchartreport
so i need to update tblorg for newly inserted rows with
tblorgchartreport, suppose there are firstname, lastname
so i need to update these fields in tblorg by triming two fields like
i need to get firstname + lastname from tblorgchartreport into
firstname of tblorg. so i have few null values in lastname fields .
so can any one tell me how to solve avoid null values while using RTRIM
or LTRIM when inserting values
waiting for reply thank you in advance
*** Sent via Developersdex http://www.codecomments.com ***Hi
You can use this way:
SELECT LTRIM(RTRIM(ISNULL(FirstName,''))) + LTRIM(RTRIM(ISNULL(LastName,''))
)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"krishna nellutla" wrote:

> Hi
> can any one solve my problem; i could not get solution for following
> query
> I have two tables like tblOrg and tblorgchartreport
> so i need to update tblorg for newly inserted rows with
> tblorgchartreport, suppose there are firstname, lastname
> so i need to update these fields in tblorg by triming two fields like
> i need to get firstname + lastname from tblorgchartreport into
> firstname of tblorg. so i have few null values in lastname fields .
> so can any one tell me how to solve avoid null values while using RTRIM
> or LTRIM when inserting values
> waiting for reply thank you in advance
>
> *** Sent via Developersdex http://www.codecomments.com ***
>|||Does this help?
use tempdb
go
create table names (f varchar(30), l varchar(30))
go
insert names (f,l) values ('krishna','nellutla')
insert names (f,l) values ('mark','allison')
insert names (f,l) values ('ben',null)
set concat_null_yields_null off
select rtrim(l) + ', ' + ltrim(f) from names
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
krishna nellutla wrote:
> Hi
> can any one solve my problem; i could not get solution for following
> query
> I have two tables like tblOrg and tblorgchartreport
> so i need to update tblorg for newly inserted rows with
> tblorgchartreport, suppose there are firstname, lastname
> so i need to update these fields in tblorg by triming two fields like
> i need to get firstname + lastname from tblorgchartreport into
> firstname of tblorg. so i have few null values in lastname fields .
> so can any one tell me how to solve avoid null values while using RTRIM
> or LTRIM when inserting values
> waiting for reply thank you in advance
>
> *** Sent via Developersdex http://www.codecomments.com ***

Abt DTS mass migration

Two servers with different name and IP
We have to transfer around 500 DTS packages to the new server. But can not restore the MSDB to new server, and also can not DTS all the packages to new server in one step. Any tips for DTS mass migration? Or we have to save as one by one manually? Thanks in advanced.Hi there,

I don't know if this helps you further, but I use the "DTSBackup 2000" tool for transferring DTS packages from one server to another. You can find out more about this tool at http://www.sqldts.com/?242

Greetings,
Carsten|||Thank u very much. Let me try...

Originally posted by CarstenK
Hi there,

I don't know if this helps you further, but I use the "DTSBackup 2000" tool for transferring DTS packages from one server to another. You can find out more about this tool at http://www.sqldts.com/?242

Greetings,
Carsten