|
this may not be the best way but a thought
write an app - which would just have this one command of stored proc execution.
Then schedule this app using windows scheduler.
|
|
|
|
|
Hey ppl, can anyone explain me this code.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER FUNCTION dbo.JoinBase(@JID INT) RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @JEX VARCHAR(200),
@Qualification VARCHAR(20)
DECLARE c1 CURSOR LOCAL FAST_FORWARD FOR
select enb from I_EM inner join Ew on I_EM.e_id=Ew.e_id and empid=@JID
ORDER BY e_name
OPEN c1
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH c1 INTO @Qual
IF (@@FETCH_STATUS = 0)
BEGIN
SET @JEX = IsNull(@JEX + ',', '') + @Qual
END
END
CLOSE c1
RETURN @JEX
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
and what does this ISNULL do????
pls help somebuddy..
|
|
|
|
|
John Mecas wrote: can anyone explain me this code
It produces a comma separated list of all the data in the enb returned by the query.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
hey thanks for response..but can u explain it in some more details..... I knew it concats the values.
|
|
|
|
|
-- Alter an existing function called JoinBase
-- It takes an integer as a parameter called @JID
-- It will return a VARCHAR with a maximum size of 200 characters.
ALTER FUNCTION dbo.JoinBase(@JID INT) RETURNS VARCHAR(200)
AS
BEGIN
-- Two local variables are declared
DECLARE @JEX VARCHAR(200),
@Qualification VARCHAR(20)
-- A cursor is declared.
-- It will return the results of the SELECT statement one row for each loop
DECLARE c1 CURSOR LOCAL FAST_FORWARD FOR
select enb from I_EM inner join Ew on I_EM.e_id=Ew.e_id and empid=@JID
ORDER BY e_name
OPEN c1
-- Loops until the cursor has exhausted all the rows from the SELECT
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- This line won't even compile because @Qual doesn't exist
-- but it is supposed to get the value of enb from the SELECT
-- statement into the local variable @Qual (@Qualification)
FETCH c1 INTO @Qual
-- If the fetch was successful
IF (@@FETCH_STATUS = 0)
BEGIN
-- Append @Qual into the existing list.
-- If the list doesn't exist create one as a blank string
-- The end result is a comma separated list mad up of each
-- @Qualification/@Qual returned from the query.
SET @JEX = IsNull(@JEX + ',', '') + @Qual
END
END
-- Close the cursor
CLOSE c1
-- Returns the result to the caller
RETURN @JEX
END
To me it looks like the cursor is badly used. (Well, using a cursor is bad enough, but there are bits that just don't quite look right). If you want more information on cursors then I expect you can look it up in the documentation - Just search MSDN for it. I don't use cursors if I can help it. They are, occasionally, a necessary evil, but for the most part the should be avoided like the plague.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
Maybe this SQL can do the same thing:
select @JEX = isnull(@JEX + ',', '') + enb from I_EM inner join Ew on I_EM.e_id=Ew.e_id and empid=@JID
ORDER BY e_name
If not necessary,do not use CURSOR;P
|
|
|
|
|
That won't work.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
1-we need CURSOR to action row by row "CURSOR giving one record from select statment to effect any thing on record "
2- "Isnull" function contain two parameters the first parameter expressions to check if this expressions have null or not, if the expression have null return second parameters else return value of expressions
if you have more explain about CURSOR tell me plz
Rami Abd Alhalim
|
|
|
|
|
Guys ...
Need help on urgent basis...
I have two tables
CREATE TABLE #T1(id int,Name varchar(10))
insert into #T1 (id,Name) values (1,'Shon')
insert into #T1 (id,Name) values (2,'Julie')
CREATE TABLE #T2(id int,Reason varchar(5))
insert into #T2 (id,Reason) values (1,'X')
insert into #T2 (id,Reason) values (2,'Y')
insert into #T2 (id,Reason) values (1,'Z')
insert into #T2 (id,Reason) values (1,'K')
Now i need output like
Id Name Reason1 Reason2 Reason3 Reason4
1 Shon X Z K Null
2 Julie Y NULL NULL Null
I can restrict upto 4 reasons....
PLEASE PLEASE Help
Thanks!!
|
|
|
|
|
With simple query is not possible. however check this
SET NOCOUNT ON
DECLARE @T1_id AS Int
DECLARE @T1_Name AS Varchar(10)
DECLARE @T2_id AS Int
DECLARE @T2_Reason AS Varchar(5)
DECLARE @qryResult AS Varchar(256)
DECLARE @cnt AS Smallint
IF NOT EXISTS(SELECT * FROM tempdb..sysobjects WHERE name LIKE '#T3[_]%')
CREATE TABLE #T3(id int, Name varchar(10), Reason1 varchar(5),
Reason2 varchar(5), Reason3 varchar(5), Reason4 varchar(5))varchar(5), Reason4 varchar(5))
DELETE FROM #T3
DECLARE CurT1 CURSOR FOR SELECT * FROM #t1
OPEN CurT1
FETCH CurT1 INTO @T1_id, @T1_Name
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #T3 (id, Name) VALUES(@T1_id, @T1_Name)
SET @cnt = 0
DECLARE CurT2 CURSOR FOR SELECT * FROM #t2 WHERE id = @T1_id
OPEN CurT2
FETCH CurT2 INTO @T2_id, @T2_Reason
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cnt = @cnt + 1
SET @qryResult = 'UPDATE #T3 SET Reason' + Cast(@cnt AS Varchar(10)) + ' = '''
+ @T2_Reason + ''' WHERE id = ' + Cast(@T1_id As Varchar(5))
EXEC (@qryResult)
FETCH CurT2 INTO @T2_id, @T2_Reason
END
CLOSE CurT2
DEALLOCATE CurT2
FETCH CurT1 INTO @T1_id, @T1_Name
END
CLOSE CurT1
DEALLOCATE CurT1
SET NOCOUNT OFF
SELECT * FROM #T3
--SELECT * FROM #T1
--SELECT * FROM #T2
Regards
KP
|
|
|
|
|
Reserved words appears in Blue Color in SQL 2000 !
But in SQL 2005,some of Reserved words appears in Black color itself !
Why?
Eg: VARCHAR()
|
|
|
|
|
Go into the Options and change the font/color appearance to what you want. No big deal. Assuming you are talking about SQL Server Management Studio, black for Identifiers is the default.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Dear Friends,
This is the table I have:
ID Qty
1 3
2 4
3 7
4 2
5 1
I want to Select Group of rows those Total Qty is equal to 10.
ie.
ID Qty
Group1 3
Group1 7
Group2 3
Group2 4
Group2 2
Group2 1
Group3 7
Group3 2
Group3 1
Please help me.
Thanks
Raghavendra Hegde.
|
|
|
|
|
To solve this problem you have to Stored procedure .
so not have any group in sql can giving this result
Rami Abd Alhalim
|
|
|
|
|
Hi friend,
Here i am send u a query which is help to get the value by group. i used a table called newtable with the field of empid,empnum where empid is varchar n empnum is int. Now i used inerjoint to find the group value for each giving datas of empnum
"select t1.empid from newtable as t1 inner join (select empid from newtable where empnum=2) as t2 on t1.empid=t2.empid"
I this may solution for ur question.
If u got anything pls reply me.........
Senthil S
Software Engineer
|
|
|
|
|
declare @empid as int
select @empId=isnull(max(empId),100)+1 from tbltempemployee
select @empid
Hi this command will generate empid like
101
102 like...,
Suppose to need some alphabet letter before the empid..,
How to do that?,
Suppose before empid i like to generate "L" char value...,
It will be like this one...,
L101
L102 like...,
How to metion the "L" char before the empId in Auto generation
Regards,
Magi
|
|
|
|
|
Hi
This can be solved by fellowing inner joins method. I thing it may work
"select isnull(max(subfld),1)+1 as val from newtable as t1 inner join (select empid,substring(empid,2,datalength(empid)-1) as subfld from newtable) as t2
on t1.empid=t2.empid"
Table:- newtable
Flds:- empid,empnum
data:- (L1,L2,L3)empid,(2,5,8)empnum
i used inner join n created two tables called t1,t2 which is unique by 'empid'. t2 table having flds like empid n subfld while joining i find the substring of the empid n place it in t2's subfld. then i find the max for that ..
Pls go through above and responce me whatever u got....
thnk u
Senthil S
Software Engineer
|
|
|
|
|
you can used Reserved words @@identity
INSERT Table name DEFAULT VALUES
SELECT @@IDENTITY -- used after any action on db(insert ,update,delete)
or
SELECT IDENT_CURRENT('Table Name')-- used without action this the best
|
|
|
|
|
Hi,
I write a function using Transat-Sql(in sqlserver 2005) which returns a integer. My problem i dont know how to catch the result of my function via C#(Visual 2005). I connect to my database, but the object SqlDatareader does not give me the result of my function.
Thank you for your help
|
|
|
|
|
Use ExecuteScalar for this something like...
wrap the function into a stored proc then call the stored proc:
<br />
SqlCommand command = new SqlCommand("MyStoredProcedure", connection);<br />
command.CommandType = CommandType.StoredProcedure;<br />
object objData = command.ExecuteScalar();<br />
return objData;<br />
|
|
|
|
|
cmd=new sqlcommand("storedname",connection)
cmd.CommandType = CommandType.StoredProcedure
p1 = New SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4)
p1.Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(p1)
cmd.ExecuteNonQuery()
returnvalue= cmProducts.Parameters("@RETURN_VALUE").Value
Rami Abd Alhalim
|
|
|
|
|
Hello,
Would you please be kind to answer my question !
I have developed an ASP.NET Web application using SQL Server Express 2005 in my local PC where I have used VarBinary(MAX) for an image data storage. It is working fine in my computer. Now, I have configured my SQL Server 2005 in my production server using MyLittleAdmin, I found that, I can not set the value MAX for VarBinary, so I am forced to enter the value of varBinary = 8000. Now, When I insert image to this field more than 8KB, I get the following message,
"String or binary data would be truncated."
So, what does it mean, Is it not really possible to store image or file data in VarBinary which is more than 8KB ? if yes, then how can I configure my table for that using MyLittleAdmin ?
Regards
|
|
|
|
|
I don't know about this software you used "MyLittleAdmin" but I know that when I have a stored procedure that takes a parameter varbinary(MAX), I declare the parameter in the code with a length of -1 as follows:
procedure_Insert.Parameters.Add(
new System.Data.SqlClient.SqlParameter("@picture",
System.Data.SqlDbType.VarBinary, -1,
System.Data.ParameterDirection.Input,
false, 0, 0, "picture", System.Data.DataRowVersion.Current, null)
);
To my knowledge the maximum size of varbinary(MAX) is 2GB but I could be wrong on that one.
Hope this helps
Talal
-- If this is a post that has been helpful to you, please vote for it. Thank you!
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook
|
|
|
|
|
Hi Talal,
Thanks a lot for your reply. Actually I am new and I have never used Stored_Procedures. In my web application, I have used SQLDataSouruce to insert the image. I have insert statement:
INSERT INTO [Images] ([title], [description], [imagedata], [mimetype]) VALUES (@title, @description, @imagedata, @mimetype)
Now, this works in my computer using SQLExpress. But the problem is, I will have to specify a value for the database column of [imagedata] in my remote hosting server's SQL, but I did not need to specify the value of [imagedata] (varBinary-MAX) in SQLExpress. How can I overcome this size issue while configuring the database column ?
Regards.
|
|
|
|
|
Could it be possible that the remote SQL server is not version 2005? As I recall, SQL 2000 doesn't have the MAX feature.
-- If this is a post that has been helpful to you, please vote for it. Thank you!
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook
|
|
|
|
|