|
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
|
|
|
|
|
you may declare and use dynamic sql like this;
CREATE PROCEDURE GetRecords ( @DynamicColumnName varchar(100) ) AS
DECLARE @dynamicsql varchar(255)
SELECT @dynamicsql = 'SELECT Column1, Column2, Column3 ' +
'FROM TableName ORDER BY ' + @DynamicColumnName
EXEC(@dynamicsql)
Hope this will help.
________________
Tehmina Qureshi
****************
|
|
|
|
|
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
|
|
|
|
|
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)
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
****************
|
|
|
|
|
Hi,
Thank you very much sir.I wil follow the rules.
Veeresh.
i want to join this group
|
|
|
|
|
hi IsrarMuhammadQ
i have error to execute dynamic sql in function
|
|
|
|
|
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.
****************
|
|
|
|
|
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.
|
|
|
|
|
Hi,
Dont make it complicated.
just try this in Northwind database.
Create PROCEDURE test_GetRecords ( @DynamicColumnName varchar(100) ) AS
DECLARE @dynamicsql varchar(255)
SELECT @dynamicsql = 'SELECT * ' +
'FROM [Categories] ORDER BY ' + @DynamicColumnName + ' desc'
EXEC(@dynamicsql)
exec test_GetRecords '[CategoryName]'
________________
Tehmina Qureshi
We can have facts without thinking but we cannot have thinking without facts.
****************
|
|
|
|
|
i want use dynamic sql in function not in stored procedure
|
|
|
|
|
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.
****************
|
|
|
|
|
ok Thanks
where r u from?
|
|
|
|
|
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?
|
|
|
|
|
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
|
|
|
|
|
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
****************
|
|
|
|
|
Hi Sir,
Thanks for helping me.I got it.
Veeresh
i want to join this group
|
|
|
|
|
hi there,
can SQL contains excel sheet as an element 
note I am using SQL provided by the .Net environment
thanks
There is always something to learn
|
|
|
|
|
You can do it by saving it into one of the binary types, such as VARBINARY (assuming you're using SQL Server).
|
|
|
|
|
can you explain a little bit more
because I do not know what to do then when I want to open it with the excel?
There is always something to learn
|
|
|
|
|
You need to read it back out of the database, and save it back as an Excel spreadsheet.
|
|
|
|
|
do you know this could be accomplished with code ?
or just the pseudo code of it and I'll try to find the corresponding VBcode
?
There is always something to learn
|
|
|
|
|
Knowledgestudent wrote: just the pseudo code
Precisely so.
Knowledgestudent wrote: I'll try to find the corresponding VBcode
You are better of trying to code it yourself and learn something.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hey all,
I have a need to create a retention report in mysql/sql server that I can use to find out how many customers have canceled within 30,60,90 days of their sale date.
So my query is something like this in mysql:
<br />
SELECT t1.sale_date, t1.primary_phone, t1.cancel_date<br />
FROM n.orders as t1<br />
where t1.sale_date between "2007-08-01" and "2007-08-31"<br />
order by t1.date_entered<br />
I need to be able to say something like and t1.cancel_date <= 30 days + sale_date.
Does this make sense? I'm not sure how to do this without adding a more complex script, but you guys are the experts!
Please help! Thanks
|
|
|
|
|
In SQL-Server you would use the DateAdd function to add 30 days.
|
|
|
|
|
Use the dateadd function
<br />
where t1.cancel_date <= dateadd("d", 30, sale_date)<br />
Hope it helps
Habetis bona deum
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
