Database

30-Nov-07 19:46

Hi all,

Here iam getting RoleId and iam storing it in a variable @str2.Now
i want to use this variable data as a column name in third query i.e
@str3.Please help me on this.

alter procedure LogProc
(
@UserId varchar(500),
@Password varchar(500)
)
as
Begin
declare @str1 varchar(500)
declare @str2 varchar(500)
declare @str3 varchar(500)
select @str1=AstId from UserTable where UserId=@UserId and Password = @Password;
select @str2=RoleID from AstProf where AstId =@str1 ;
select @str3=MenuId from MenuTable where @str2='y';
select @str3;
End

exec LogProc 'veeresh','veeresh'

i want to join this group

IMQ30-Nov-07 20:45

30-Nov-07 20:45

you cannot pass the Column dynamically unless using Dynamic SQL.
Now the question is that how to use dynamic sql
declare a variable assign it TSQL with any dynamic values
execute it .

________________
Tehmina Qureshi
****************

veereshIndia30-Nov-07 21:06

30-Nov-07 21:06

Hi Sir,

Iam new to sql.Can you tel me how to use dynamic sql in my query.

Veeresh

i want to join this group

IMQ30-Nov-07 21:16

30-Nov-07 21:16

you may declare and use dynamic sql like this;

CREATE PROCEDURE GetRecords ( @DynamicColumnName varchar(100) ) AS

/* Create a variable @SQLStatement */
DECLARE @dynamicsql varchar(255)
    
/* Enter the dynamic SQL statement into the*/
/* variable @SQLStatement*/
SELECT @dynamicsql = 'SELECT Column1, Column2, Column3 ' +
                       'FROM TableName ORDER BY ' + @DynamicColumnName
    
/* Execute the SQL statement*/
EXEC(@dynamicsql)

Hope this will help.

________________
Tehmina Qureshi
****************

veereshIndia30-Nov-07 21:26

30-Nov-07 21:26

Dear Sir,

Thanks for your help.But it's not comming here.Can you tel me how to use
that in this query.Here is my query

alter procedure LogProc
(
@UserId varchar(500),
@Password varchar(500)
)
as
Begin
declare @str1 varchar(500)
declare @str2 varchar(500)
declare @str3 varchar(500)
declare @str4 varchar(500)
select @str1=AstId from UserTable where UserId=@UserId and Password = @Password;
select @str2=RoleID from AstProf where AstId =@str1 ;
set @str4=@str2;
select @str3=MenuId from MenuTable where @str4='y';
exec(@str4);
select @str3;
End

exec LogProc 'veeresh','veeresh'

But its giving error Like this

(1 row(s) affected)

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'R1'.

i want to join this group

IMQ30-Nov-07 21:38

30-Nov-07 21:38

Veer,
you did not fully understood the concept of dynamic sql.
so 1st you have to understand what the dynamic sql is;
Dynamic SQL is actually the complete sql statement, including all the necessory clauses
required for executing the sql statemnt like as "select * from tablename"
so if u hav a variable declared for dynamic sql u must assign a complete statement in it.
here in ur sp u did wrong.
Example:Declaration of dynamic sql

Declare @DynamicSql varchar(2000)
set @DynamicSql = 'SELECT * FROM MyTable'

now you may execute the like this

exec(@DynamicSql) /* See ! Here in execute i've given the variable name nott any procedure */

You may do aything which you want to with Dynamic SQL.
I did not understand what you want to select in you stored procedure (as i dont hav enough time to understand the code coz im at my job. May be after few time i'll correct your code and post it back to u.)
If you have got an idea about Dynamic SQL you can do whatever you want.
Still hav problem? You are welcome to query.
Thanks

________________
Tehmina Qureshi
****************

veereshIndia30-Nov-07 22:05

30-Nov-07 22:05

Hi,

Thank you very much sir.I wil follow the rules.

Veeresh.

i want to join this group

Rami Said Abd Alhalim30-Nov-07 22:16

30-Nov-07 22:16

hi IsrarMuhammadQ
i have error to execute dynamic sql in function

IMQ30-Nov-07 22:19

30-Nov-07 22:19

Send the code and error msg
i'll try to figure it out
Can u describe me in words what you want to do?
just describe it clearly i'll try to make you the actual understanding about what u are asking.

________________
Tehmina Qureshi
We can have facts without thinking but we cannot have thinking without facts.
****************

Rami Said Abd Alhalim30-Nov-07 22:27

30-Nov-07 22:27

this example :
--------------------------------------
CREATE FUNCTION test_fn
(
@x int
)
RETURNS
@table TABLE
(
c1 int
)
AS
BEGIN
EXEC('select * from @table')

RETURN
END
-----------------------------

Msg 443, Level 16, State 2, Procedure _fn__Get_All_References, Line 35
Invalid use of 'EXECUTE' within a function.

IMQ30-Nov-07 22:37

30-Nov-07 22:37

Hi,
Dont make it complicated.
just try this in Northwind database.

Create PROCEDURE test_GetRecords ( @DynamicColumnName varchar(100) ) AS

/* Create a variable @dynamicsql */
DECLARE @dynamicsql varchar(255)
    
/* Enter the dynamic SQL statement into the*/
/* variable @dynamicsql*/
SELECT @dynamicsql = 'SELECT * ' +
                       'FROM [Categories] ORDER BY ' + @DynamicColumnName + ' desc'
    
/* Execute the SQL statement*/
EXEC(@dynamicsql)

exec test_GetRecords '[CategoryName]'

________________
Tehmina Qureshi
We can have facts without thinking but we cannot have thinking without facts.
****************

Rami Said Abd Alhalim30-Nov-07 22:51

30-Nov-07 22:51

i want use dynamic sql in function not in stored procedure

IMQ30-Nov-07 23:12

30-Nov-07 23:12

Oops, sorry - Basically, no, you cannot
use dynamic SQL in a function, and you probably shouldn't use it anyway.
Writing to a temporary table is prohibited by functions.

________________
Tehmina Qureshi
We can have facts without thinking but we cannot have thinking without facts.
****************

Rami Said Abd Alhalim30-Nov-07 23:19

Natural and Surrogate Keys

30-Nov-07 23:19

ok Thanks

where r u from?

Brady Kelly30-Nov-07 19:01

Brady Kelly

30-Nov-07 19:01

I'm busy designing a little sales database, and I would like to include a natural key in addition to my favourite surrogate (identity) keys. My question here is about auto-populating the natural key.

I would like to, on creation of a new customer for example, default the natural key (CustNo) to the surrogate key (custId). However, in a databound application, when I insert new records, the surrogate key assigned by ADO.NET changes. Here I would like to use a trigger to only change the natural key if it hasn't been assigned explicitly. This would be easy if I left CustNo null on the client, and used an insert trigger, but I want to default the CustNo to custId on the client, so it is never null. Should I maybe leave it null until explicitly assigned, but show the value of custId for CustNo when CustNo is null? Is there any other, neater way to do this?

How to store result of a query in a variable

veereshIndia30-Nov-07 18:29

Re: How to store result of a query in a variable

30-Nov-07 18:29

Hi All,

Please tel me how to store result of a query in a variable.
And I want to use that variable in another query's in where condition
Here below is my code.

alter procedure LogProc
(
@UserId varchar(500),
@Password varchar(500)
)
as
Begin
declare @str1 varchar(500)
declare @str2 varchar(500)
declare @str3 varchar(500)
set @str1 =( ' select AstId from UserTable where UserId = ''' + @UserId + ''' and Password = ''' + @Password + '''' )
exec(@str1);
set @str2=(' select RoleID from AstProf where AstId = ''''' + @str1 + '''''')
exec(@str2);
End

exec LogProc 'veeresh','veeresh'

i want to join this group

IMQ30-Nov-07 19:07

Re: How to store result of a query in a variable

30-Nov-07 19:07

Hi,
you can store result of the query into a variable like this;

select @str1=AstId from UserTable where UserId=@UserId and Password=@Password

Here @str1 will store the AstID in it.

the complete procedure will liook like this:

alter procedure LogProc
(
@UserId varchar(500),
@Password varchar(500),
@str1 int output,
@str2 int output
)
as
Begin
declare @str3 varchar(500) /* why u hav declare this variable ???? */
select @str1=AstId from UserTable where UserId @UserId and Password = @Password
select @str2=RoleID from AstProf where AstId =@str1

select @str1,@str2
End

exec LogProc 'veeresh','veeresh'

you may use @str1 and @str2 in your code .

________________
Tehmina Qureshi
****************

veereshIndia30-Nov-07 19:17