Configuration fIle

I'm using stored procedures in a database that use data in other databases. I refer these data as DataBaseName.dbo.TableName. It works fine. But, i just thought, what if the name of the databases change?

Is there anyway to use some sort of variables to refer the databases? like a configuration file used in programming applications.

Thanks

You can do one of the below:

1. In SQL Server 2005, you can create synonyms for the tables in the other database. And when the name of the database changes you will have to drop & recreate the synonym

2. In older versions of SQL Server, you can create views that point to the tables in the other database and do the same.

Concurrency issues in stored procedures

I have a question regarding concurrency in stored procedures. I have a stored procedure with the following flow of control:

-- A couple of select statements here.

-- (placeholder - see below)

-- A couple of updates based on the data obtained
-- from the above select statements.

I'm concerned that while the stored procedure is running, if right when it gets to the spot in the code marked 'placeholder', the scheduling mechanism of SQL Server switches to another thread that contains requests from another client which makes changesto the data in the previous select statements. This will make my updates bad, since they rely on data (from the select statements)that has been modified.

I guess I'm curious what I would need to do to lock the records in the select statements until after my update statements are completed. Thanks in advance for any advice!

EverettBump

Concurrency

Do single commands (or stored procedures) execute concurrently, or they are executed one by one. How do you perform a lock during the execution of a command (or stored procedure).

Single commands are 'implied' transactions, that is the command executes completely, or nothing is done.

For multiple commands, where there is a need to make sure the several statements execute in a 'all or nothing' scenario, use TRANSACTIONS.

Refer to Books Online, Topic: Transactions

|||

Other good topics in the BOL are "Isolation mode" and locking hints.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Concerning .net and SQL Procedures

Recently i had to write a script in sql to compare multiple tables to get a result of items that do not conform to certain business logic. In doing so i wrote all of this information into a sql parameter which branches out to a few other parameters within the parameter.

Now if you need the code just let me ask, but this is a general question to see if it has occured for anyone else.

The problem i am recieving is when i access the code from a .net windows application it tells me:

Error Message:
Insert Error: Column name or number of supplied values does not match table definition.
Insert Error: Column name or number of supplied values does not match table definition.

Procedure Errored On: val_GetDuplicateItemsFromAssignment
Line Number: 16

However when i run the sql parameter within SQL it accesses it just find. This is using the same parameter values.

Does anyone know why this could be happening?

Please do show the code used to insert the values.

Concatenating output parameters

I have a stored procedure (PROC1) that calls another stored procedure (PROC2
)
multiple times. Both procedures have an output parameter and I am wondering
if I can take the multiple output parameters from PROC2 and combine them int
o
the parameter that is output for PROC1. Example...
Say that PROC2 is called 4 times and the output from each call is this
1
2
3
4
I want the output of PROC1 to be 1234. Any help is appreciated.
ThanksYes, create a varchar() column, of appropriate size, and each time you call
the stored proc (Proc2) append the return value to the VarChar variable
Now, the output of the stored proc can be in an output parameter, or just
the return value... WHich is it? If it's an Output parameter
Declare @.Var VarChar(10)
Set @.Var = '' -- Set as empty string to avoid Null propagation issues
Exec PROC2 ..., <@.OutputParam OUTPUT, ...
Select @.Var = @.Var + @.OutputParam
-- ---
Exec PROC2 ..., <@.OutputParam OUTPUT, ...
Select @.Var = @.Var + @.OutputParam
-- ---
Exec PROC2 ..., <@.OutputParam OUTPUT, ...
Select @.Var = @.Var + @.OutputParam
-- ---
Exec PROC2 ..., <@.OutputParam OUTPUT, ...
Select @.Var = @.Var + @.OutputParam
-- ---
"Andy" wrote:

> I have a stored procedure (PROC1) that calls another stored procedure (PRO
C2)
> multiple times. Both procedures have an output parameter and I am wonderi
ng
> if I can take the multiple output parameters from PROC2 and combine them i
nto
> the parameter that is output for PROC1. Example...
> Say that PROC2 is called 4 times and the output from each call is this
> 1
> 2
> 3
> 4
> I want the output of PROC1 to be 1234. Any help is appreciated.
> Thanks|||Example:
use northwind
go
create procedure proc1
@.p1 int output
as
set nocount on
set @.p1 = (select top 1 orderid from dbo.orders order by newid())
return @.@.error
go
create procedure proc2
@.p1 varchar(128) output
as
set nocount on
declare @.i int
declare @.j int
declare @.s varchar(128)
set @.i = 1
set @.s = ''
while @.i <= 4
begin
set @.j = null
exec proc1 @.J output
if @.j is not null
set @.s = @.s + case when @.s > '' then '|' else '' end + ltrim(@.j)
set @.i = @.i + 1
end
set @.p1 = @.s
return 0
go
declare @.s varchar(128)
exec proc2 @.s output
print @.s
go
drop procedure proc2, proc1
go
AMB
"Andy" wrote:

> I have a stored procedure (PROC1) that calls another stored procedure (PRO
C2)
> multiple times. Both procedures have an output parameter and I am wonderi
ng
> if I can take the multiple output parameters from PROC2 and combine them i
nto
> the parameter that is output for PROC1. Example...
> Say that PROC2 is called 4 times and the output from each call is this
> 1
> 2
> 3
> 4
> I want the output of PROC1 to be 1234. Any help is appreciated.
> Thanks|||Thanks a lot for your help on this. I should have mentioned that I am using
dynamic SQL. Here is a sample of my code. I have declared all of the
variables in my code and am trying to have @.p as my output. I know you
cannot use Set in dynamic SQL but am hoping I have other options.
@.BottomLimit and @.TopLimit are .5
@.TableName is the name of the table
@.PreviousLoad and @.CurrentLoad are passed in as '200408' and '200409'
The tables I am looking at are partitioned and I am comparing last month's
row count to this month's. Just to give you a better idea of the code below
.
Set @.SQL = 'Set @.p = (Select Case When(((SELECT Count(*) FROM ' + @.TableName
+ @.CurrentLoad + ' ) Between
( SELECT Count(*) - Count(*) * ' + @.BottomLimit + ' FROM ' + @.TableName +
@.PreviousLoad + ' ) AND
( SELECT Count(*) + Count(*) * ' + @.TopLimit + ' FROM ' + @.TableName +
@.PreviousLoad + ' )))
THEN ''QC has passed for ' + @.TableName + @.CurrentLoad + '''
ELSE ''QC has failed for ' + @.TableName + @.CurrentLoad + '''END)'
exec(@.SQL)
Any help you can provide is greatly appreciated!!!!!
"Alejandro Mesa" wrote:
> Example:
> use northwind
> go
>
> create procedure proc1
> @.p1 int output
> as
> set nocount on
> set @.p1 = (select top 1 orderid from dbo.orders order by newid())
> return @.@.error
> go
> create procedure proc2
> @.p1 varchar(128) output
> as
> set nocount on
> declare @.i int
> declare @.j int
> declare @.s varchar(128)
> set @.i = 1
> set @.s = ''
> while @.i <= 4
> begin
> set @.j = null
> exec proc1 @.J output
> if @.j is not null
> set @.s = @.s + case when @.s > '' then '|' else '' end + ltrim(@.j)
> set @.i = @.i + 1
> end
> set @.p1 = @.s
> return 0
> go
> declare @.s varchar(128)
> exec proc2 @.s output
> print @.s
> go
> drop procedure proc2, proc1
> go
>
> AMB
>
> "Andy" wrote:
>|||andy,
if you absolutely have to use dynamic SQL, then you need to switch from
using "EXEC" to using a built-in System stored proc called sp_ExecuteSQL()
... Look it upo in the Books On LIne.
When you use EXEC, the called stored proc runs in it's own context, and the
value of Output parameters is not available to the caller stored proc. If
you use so_ExecuteSQL, then toy CAN define an output Parameter in the Caller
SP whose value will be modified by the Called SP, and will later be avaliabl
e
back in the caller after the SP runs...
"Andy" wrote:
> Thanks a lot for your help on this. I should have mentioned that I am usi
ng
> dynamic SQL. Here is a sample of my code. I have declared all of the
> variables in my code and am trying to have @.p as my output. I know you
> cannot use Set in dynamic SQL but am hoping I have other options.
> @.BottomLimit and @.TopLimit are .5
> @.TableName is the name of the table
> @.PreviousLoad and @.CurrentLoad are passed in as '200408' and '200409'
> The tables I am looking at are partitioned and I am comparing last month's
> row count to this month's. Just to give you a better idea of the code bel
ow.
> Set @.SQL = 'Set @.p = (Select Case When(((SELECT Count(*) FROM ' + @.TableNa
me
> + @.CurrentLoad + ' ) Between
> ( SELECT Count(*) - Count(*) * ' + @.BottomLimit + ' FROM ' + @.TableName
+
> @.PreviousLoad + ' ) AND
> ( SELECT Count(*) + Count(*) * ' + @.TopLimit + ' FROM ' + @.TableName +
> @.PreviousLoad + ' )))
> THEN ''QC has passed for ' + @.TableName + @.CurrentLoad + '''
> ELSE ''QC has failed for ' + @.TableName + @.CurrentLoad + '''END)'
> exec(@.SQL)
>
> Any help you can provide is greatly appreciated!!!!!
> "Alejandro Mesa" wrote:
>