|
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
|
|
|
|
|
Thanks guys!
I still have a problem with my formatting somewhere because in mysql I get the error "no database is selected". I played around with the query and got another error saying that "n.dateadd does not exist". It seems like I need some parenthesis or something. Does anyone know the specifics of how to use this function right in mysql?
I've searched around, and I've had problem fitting it to my situation.
Thanks all!
|
|
|
|
|
Ok so dateadd in mysql is just date_add! Doh! My syntax is still off though because I'm getting 1064 errors.
I've got:
<br />
SELECT * FROM orders<br />
where date_entered between "2007-08-01" and "2007-08-31" and cxldate <> "0" and cxldate <= date_add('day', 30, date_entered)<br />
cxldate stands for cancel date
I get this error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '30, date_entered)' at line 2
|
|
|
|
|
Try here[^] for some more information.
|
|
|
|
|
Hi
I'm working on a dataset and I currently would like to perform the follwing statement on a tableadapter:
SELECT ElementName FROM Elements
WHERE
ElementId IN (69,70,88,123)
I wrote the following query in the table adapter
SELECT ElementName FROM Elements
WHERE
ElementId IN (@ListOfIds)
I've set the property of this parameter to be a string. But if I do "Preview Data"... It says me that he Can't convert the given string to int.....
Where am I doing wrong???
|
|
|
|
|
You can't do this because you are passing in a string containing the list. You need to do this by either writing a function to parse it or by writing dynamic SQL.
|
|
|
|
|
I'm hoping someone can give me some ideas. I have a program that works for most people that inserts photos into a database using a data adapter.
The problem is on one PC (so far that I've found) it doesn't insert the record or throw an error.
The unusual thing is the user can log into another PC and it works fine and other users can log into that PC and it doesn't work for them.
What do I look for on that PC that could be causing the problem? I am lost as to what to do to fix this PC. I don't think this is a programing question, but rather a hardware or registry question. Is there a better forum to post this in?
The biggest problem is just that the Update function returns that it updated 1 record which is what I expect, but the record is not there when I query it.
Thanks
-- modified at 13:52 Friday 30th November, 2007
Brent
|
|
|
|
|
dbrenth wrote: What do I look for on that PC that could be causing the problem?
Find what is different in that particular PC. It could be one of a multitude of things.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
