How do I make the name of a variable dynamic?

Hi guys, I have the following store procedure:

PROCEDURE dbo.AddSearchColumn (@.A1, @.A2. @.A3, @.A4, @.A5) AS
Declare @.cElements cursor,@.DocNum varchar(100)
BEGIN
Set @.cElements = cursor for select FirstNaname rom dbo.DocTable1
open @.cElements
fetch NEXT from @.cElements into @.DocNum

while (@.@.FETCH_STATUS = 0)
begin
if (@.DocNum==@.A1 //Here is what I need to do: I want use @.A1 at the first loop step, @.A2 at the second, and so on
...... // @.A1, @.A2 are the parameters in input
end
END

close @.cElements
DEALLOCATE @.cElements

I hope my explanation is clear.

Please, give some hints.
Thanks for your time,

Christian Malatesti

Can you explain in more detail what it is that you are trying to do? From what I understand you want to compare the values in the parameters against "FirstNaname" from table docTable1?

|||

What I am trying to do is this:

I have several parameters in input: @.A1, @.A2, @.A3, @.A4,...and so on (they are more than four) that I need to use

while looping through the elements of the cursor.

I know the order that the input parameters need to be used.

At the first step of the loop I have to compare the first element retrieved by the cursor with @.A1.

At the second step of the loop I have to compare the second element retrieved by the cursor with @.A2.

At the third step of the loop I have to compare the third element retrieved by the cursor with @.A3.

and so on...

What I would like to accomplish is creating a variable (@.dynamicVariable) that at the first contains the value of @.A1, atthe second step, @.dynamicVariable contains the value of @.A2, andso on...

Below is a basic example.

Declare@.dynamicVariable , @.counter int

set @.counter=1;

while (@.@.FETCH_STATUS = 0)
begin

set @.dynamicVariable = "@.A" + ToString(@.counter)
if (@.DocNum==@.dynamicVariable....

//something like this:

set @.counter=@.counter + 1;
end
END

Thanks for responding me.

Christian Malatesti

|||Is the number of parameters fixed?|||YES, i have 17 parameters in input|||And you will always have 17 values returned from your SQL statement?|||

I do not have any parameters in output.

The input parameters are used to perfrom various INSERT, that I did not include in the post.

Christian

|||

Perhaps you can get the results into 2 tables (table variables) each with identity columns and compare by rowid?

How do I make a field automatically get its values from another fi

Hi,
In my table I have a "PaymentDate" field which is used to store payment
schedules for our clients. I want to add a new field in the same table and
call it "UpdatedPaymentDate" field as most of the time our clients don't
stick to original payment schedules.
How do I make the default value of this new "UpdatedPaymentDate" field to be
the values in the "PaymentDate" field? Do I have to use a trigger for this?
Is there a way to do this without using triggers?
If necessary I'll post the table script but I don't it's necessary for this
simple question. Both fields are in the same table and their data type is
smalldatetime for both.
--
Thanks,
SamSam wrote:
> Hi,
> In my table I have a "PaymentDate" field which is used to store payment
> schedules for our clients. I want to add a new field in the same table and
> call it "UpdatedPaymentDate" field as most of the time our clients don't
> stick to original payment schedules.
> How do I make the default value of this new "UpdatedPaymentDate" field to
be
> the values in the "PaymentDate" field? Do I have to use a trigger for this
?
> Is there a way to do this without using triggers?
> If necessary I'll post the table script but I don't it's necessary for thi
s
> simple question. Both fields are in the same table and their data type is
> smalldatetime for both.
> --
> Thanks,
> Sam
I'm assuming you'll use stored procs for your inserts of course. So use
an optional parameter and assign the default in the proc:
CREATE TABLE dbo.PaymentSchedule (PaymentDate SMALLDATETIME NOT NULL,
UpdatedPaymentDate SMALLDATETIME NOT NULL /* ... key? */);
GO
CREATE PROCEDURE dbo.usp_PaymentScheduleInsert
(
@.PaymentDate SMALLDATETIME,
@.UpdatedPaymentDate SMALLDATETIME = NULL
)
AS
INSERT INTO dbo.PaymentSchedule (PaymentDate, UpdatedPaymentDate)
VALUES (@.PaymentDate, COALESCE(@.UpdatedPaymentDate,@.PaymentDat
e));
GO
EXEC dbo.usp_PaymentScheduleInsert
@.PaymentDate = '2006-04-30T00:00:00.000' ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

How do i limit number of rows in a table based on dates?

Hi,
My application is written in c# and uses sql Express to store
information relating to what time employees clock in/out of work each
day.
How can i restrict the amount of rows in my attendance database table
so that entries older than 3 months are automatically deleted from the
database?
Thanks in advance!
KHi
I'd create a stored procedure that does deletion and then create a
job(weekly...) that will run this SP
<kls.systems@.btinternet.com> wrote in message
news:1176719432.941536.219550@.o5g2000hsb.googlegroups.com...
> Hi,
> My application is written in c# and uses sql Express to store
> information relating to what time employees clock in/out of work each
> day.
> How can i restrict the amount of rows in my attendance database table
> so that entries older than 3 months are automatically deleted from the
> database?
> Thanks in advance!
> K
>|||Thanks for the help uri, I thought that sql express couldnt use
scheduled jobs though as it doesnt use sql agent?
Thanks again.|||Ah I see, did not read properly your post
Perhaps here you will find some info
Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
<kls.systems@.btinternet.com> wrote in message
news:1176721669.824203.42940@.l77g2000hsb.googlegroups.com...
> Thanks for the help uri, I thought that sql express couldnt use
> scheduled jobs though as it doesnt use sql agent?
> Thanks again.
>|||How about using a .sql script file, execute it using SQLCMD (in a .bat file) and use the Windows
scheduler to execute that .bat file?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<kls.systems@.btinternet.com> wrote in message
news:1176721669.824203.42940@.l77g2000hsb.googlegroups.com...
> Thanks for the help uri, I thought that sql express couldnt use
> scheduled jobs though as it doesnt use sql agent?
> Thanks again.
>|||On 16 Apr, 12:22, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Ah I see, did not read properly your post
> Perhaps here you will find some info
> Automating Database maintenance in SQL 2005 Express Edition Part Ihttp://www.sqldbatips.com/showarticle.asp?ID=27
> Automating Database maintenance in SQL 2005 Express Edition Part IIhttp://www.sqldbatips.com/showarticle.asp?ID=29
> <kls.syst...@.btinternet.com> wrote in message
> news:1176721669.824203.42940@.l77g2000hsb.googlegroups.com...
>
> > Thanks for the help uri, I thought that sql express couldnt use
> > scheduled jobs though as it doesnt use sql agent?
> > Thanks again.- Hide quoted text -
> - Show quoted text -|||Thanks very much for all of your help, I'll try setting up a windows
scheduled job to run an sql script to do this as suggested in the
articles you provided links for.
I just thought there would have been a quick and easy setting within
management studio express i could configure to do it but nevermind!
Thanks very much again for your help Uri, very much appreciated.

how do i get the returned id from the store proceedure in asp c#?

my store proceedure gets the id:

CREATE PROCEDURE createpost(
@.userID integer,
@.categoryID integer,
@.title varchar(100),
@.newsdate datetime,
@.story varchar(250),
@.wordcount int
)
as
DECLARE @.newNewsID integer

Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)
Values (@.userID, @.categoryID, @.title, @.newsdate, @.story, @.wordcount)

SELECT @.newNewsID = @.@.IDENTITY

then im calling it in the asp:

con = new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");
con.Open();

cmdselect = new SqlCommand("createpost", con);
cmdselect.CommandType = CommandType.StoredProcedure;

cmdselect.Parameters.Add("@.userID", userID);
cmdselect.Parameters.Add("@.categoryID", categoryID );
cmdselect.Parameters.Add("@.title", title.Text );
cmdselect.Parameters.Add("@.newsdate", newsdate.Text );
cmdselect.Parameters.Add("@.story", story.Text );
cmdselect.Parameters.Add("@.wordcount", "1" );


int valueinserted = cmdselect.ExecuteNonQuery();


Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id="+valueinserted);

con.Close();

as you can see im using the valueinserted but thats just returning 1, but im guessing that means it sucessful. but i want the id of the new record! any idea how ?

Hi,

you need to either specify a RETURN or make the @.newNewsID an output parameter.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconinputoutputparametersreturnvalues.asp

|||

Or use the SqlDataReader object like this:

1CREATE PROCEDURE createpost(2 @.userID integer,3 @.categoryID integer,4 @.titlevarchar(100),5 @.newsdatedatetime,6 @.storyvarchar(250),7 @.wordcountint8)9as1011Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)12Values (@.userID, @.categoryID, @.title, @.newsdate, @.story, @.wordcount)1314SELECT@.@.IDENTITY

and in  code:2con =new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");3int valueinserted = 0;45try6 cmdselect =new SqlCommand("createpost", con);7 cmdselect.CommandType = CommandType.StoredProcedure;89 cmdselect.Parameters.Add("@.userID", userID);10 cmdselect.Parameters.Add("@.categoryID", categoryID );11 cmdselect.Parameters.Add("@.title", title.Text );12 cmdselect.Parameters.Add("@.newsdate", newsdate.Text );13 cmdselect.Parameters.Add("@.story", story.Text );14 cmdselect.Parameters.Add("@.wordcount","1" );1516 cmdselect.Connection.Open();17 SqlDataReader reader = command.ExecuteReader();18if( reader.HasRows() )19 {20 reader.Read();21 valueinserted = Convert.ToInt32( reader[0] );22 }23catch( Exception ex)24{25//handle the exception somehow26}27finally28{29if( con !=null and conn.State != ConnectionState.Closed )30 con.Close();31}3233Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id="+valueinserted);

sql

How do I find I am administrator?

Thanks Hari.
Since I am db_owner, why I am not able to create new store procedure, view
and table? It gives me error "CREATE TABLE permission denied in database
'myTestDB'.
Your suggestion would be great help.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eVNtu1$GEHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi Sunny,
> 1.What is that means. I do not have admin rights to whole server?
> Based on the output you fell inside the 'db_owner' role in your database.
> This ensure that you can do any activiites inside that database.
> But you not in the part of 'SYSADMIN' role, which is the server wide
role.
> Due to that your SP_HELPLOGINS failed. This command will list the details
of
> all Logins who can access SQL server.
> 2.What is that means. I do not have admin rights to whole server?
> Yes, You have full access to only ur database.
> 3.Is package admin is different than server adminn?
> Yes, While saving the package you can mention a Owner password. That might
> be needed while accessing the exiting package.
> Thanks
> Hari
> MCDBA
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:OupOe69GEHA.1368@.TK2MSFTNGP11.phx.gbl...
am
> SQL
execute
does
knowwing
good
of
> installed
there
> manager
>
Hi Sunny,
It seems, the Administrator has denied access to "Create table" , "Create
Procedure" and "Create View" for your SQL server user using the
DENY statement;
deny create table to <user>
go
deny create procedure to <user>
go
deny create view to <user>
In this case even if you are db_owner for a database you will not able to do
Create table / Create View or Create Procedure.
If required ask your administrator to Grant back those previlages using
GRANT statement.
The below previlages can be denied from a DB_OWNER by administartor;
CREATE FUNCTION
CREATE PROCEDURE
CREATE RULE
CREATE TABLE
CREATE VIEW
BACKUP DATABASE
BACKUP LOG
Thanks
Hari
MCDBA
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:eiDTikBHEHA.324@.tk2msftngp13.phx.gbl...
> Thanks Hari.
> Since I am db_owner, why I am not able to create new store procedure, view
> and table? It gives me error "CREATE TABLE permission denied in database
> 'myTestDB'.
> Your suggestion would be great help.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eVNtu1$GEHA.1528@.TK2MSFTNGP09.phx.gbl...
database.
> role.
details
> of
might
I
> am
access
> execute
> does
> knowwing
> good
> of
in
> there
>