|
Ca any I tell me the maximum number of input or output parameter a sql stored procedure can have?
|
|
|
|
|
In Books Online, the sql server help, search for "maximum capacity" - it will tell you the answer and loads more useful/interesting (boring?) bits of information.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks Bob. This is really great.
|
|
|
|
|
2,100
The world is a stage and most of us are desperately unrehearsed.
—Sean O’Casey, Playwright
|
|
|
|
|
|
Just a simple addin that does things locally. Not a full and sophisticated web solution or an addin that requires access to a webserver,etc..
I need to be able to add sort of Todo markers in my source code, and be able to easily get to the source code line. Just simple things, in order to manage things myself. not something for team work.
Thank you in advance.
|
|
|
|
|
Wrong site, try rentacoder.com.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
How to unlock a table in sql server 2005? I tried killing the transaction which has a lock on the table by finding the spid through the sp “sp_lock” but still I’m not able to unlock the table.
A simple select query on this table with no “where” clause takes lot of time, resulting in Timeout Expired error. The table structure is also pretty simple with only one clustered index and the table has got only one record.
Please let me know what I can do in this scenario.
Thanks
meeram395
Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.
|
|
|
|
|
Or you could use WITH(NOLOCK) if you just want to query the table.
|
|
|
|
|
The transaction has to rollback, so that takes as long (or even longer) than the transaction had been running when you killed it. Everything should go back to normal eventually, if not then you may have to restart the sql server instance.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
You didn't mention the lock type on the locked row. If there is X lock present, you cannot select from that row (in normal situation). You'll have to wait that the X lock is gone or kill the transaction that's holding the lock (if it's not expected to hold it).
The problem you're facing may be a result of a transaction that isn't ended as it was supposed to (either rollback or commit).
Also you may want to read through the article I wrote.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Below is my store procedure
when i call GetPurchaseOrders null, 2,null,null,'Vendor','Descending'
it gives error
"Conversion failed when converting datetime from character string"
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <author,,name>
-- Create date: <create>
-- Description: <description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetPurchaseOrders]
-- Add the parameters for the stored procedure here
(
@Order_no bigint,
@VendorId int,
@FromDate datetime,
@ToDate datetime,
@Sort_by varchar(15),
@Sort_order varchar(15)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT p.Order_No as OrderNo,v.Vendor_Name as [Vendor], Order_date as OrderDate from
Purchase p
join
Vendor as v
on p.Vendor_ID=v.Vendor_ID
where p.Order_No=COALESCE(@Order_no,p.Order_No)And
p.Vendor_id =COALESCE(@VendorId , p.Vendor_id )And p.delivered='N'
ORDER BY
CASE @Sort_by
WHEN 'Order No' THEN p.ORDER_NO
WHEN 'Date' THEN p.order_date
WHEN 'Vendor' THEN v.Vendor_Name
END desc
end
“You will never be a leader unless you first learn to follow and be led.”
–Tiorio
"Coming together is a beginning, staying together is progress, and working together is success." Henry Ford
|
|
|
|
|
Dear Tiorio,
Since you were sorting on the basis of Parameter provided and it deals
with 3 columns of different data type that is why you were getting the error:
"Conversion failed when converting datetime from character string"
Just Convert the columns to Varchar will give you the desired results of yours.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <author,,name>
-- Create date:
-- Description: <description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetPurchaseOrders]
-- Add the parameters for the stored procedure here
(
@Order_no bigint,
@VendorId int,
@FromDate datetime,
@ToDate datetime,
@Sort_by varchar(15),
@Sort_order varchar(15)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT p.Order_No as OrderNo,v.Vendor_Name as [Vendor], Order_date as OrderDate
from
Purchase p
join
Vendor as v
on p.Vendor_ID=v.Vendor_ID
where p.Order_No= COALESCE(@Order_no,p.Order_No) And
p.Vendor_id = COALESCE(@VendorId , p.Vendor_id ) And p.delivered='N'
ORDER BY
CASE @Sort_by
WHEN 'Order No' THEN CONVERT(VARCHAR, p.ORDER_NO)
WHEN 'Date' THEN CONVERT(VARCHAR, p.order_date) WHEN 'Vendor' THEN v.Vendor_Name
END desc
end
Hope this answers your question.
Thanks and Regards,
Umair Feroze
|
|
|
|
|
Looks like it may be coming from the Order_date field. Is that field's datatype datetime or a character type?
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Hey Guys,
Here is my issue -
I have a table with 3 date fields. (Entry Date, Pending Date, Accounting Date) -> in my GridView I need to represent this value in 1 column called (Report Date). My rules are if Accounting Date is null use Finalized Date, if Pending date is null use Entry Date. This rule has to work on a row by row basis as I am returning 100's of rows.
Now if this is better done in the code - I can do that also.
Any help would greatly be appreciated.
Thank you.
|
|
|
|
|
Well, depending on whether or not your DBMS supports it, you could use the CASE statement to accomplish this, or you could use a COALESCE method,e.g.
SELECT COALESCE(AccountingDate, PendingDate, EntryDate) AS RuleDate ... This assumes that one of these columns has a date value in it. Basically, coalesce falls through from one entry to the next until it finds a none-null value. BTW - I'm assuming that you got your logic a bit wrong in your statement and you mean Pending Date and not Finalized Date.
|
|
|
|
|
CASE WHEN Accounting Date IS NULL THEN Finalized Date WHEN Pending date IS NULL THEN Entry Date END
etc.
|
|
|
|
|
Hi Everyone,
Ive got a Small Sql Server DB which Holds 3 Variables that i want To Update But Not Necessarily togther,Meaning: One time i want to update the first two Variables, the other one ill need to update the other 2, which leads me up to 9 options for updating queries..
Now my question is.. is there any way to write 1 query (With Stored Procedure/not) that somehow ,someway "sees" that if no parameter was sent to update , it will use a default value which ill define ?
I hope i explained myself well
thanks alot !
|
|
|
|
|
I'm not sure that I completely understand what you're trying to do, so this may not be what you're looking for. You can supply a default for each parameter to a stored procedure where the parameters are declare at the top of the procedure, but simply putting an equal sign ( = ) after the parameter's data type and then putting the value; like so:
create procedure dbo.DoSomething
@param1 int = -1,
@param2 datetime = getdate(),
@param3 char(1) = 'M'
as
....
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Thanks For your reply Ben.
thats a half of what i ment,
I'll try to rephrase myself.. is there any way i can check if a null variable has been sent into the query and to give another value to it in the stored procedure?
For example: TextBox1 Contains 1 Variable.. when i send it empty (and sometimes i need it empty) i want to give it another value.
Thanks!
|
|
|
|
|
Try something like this (not necessarily correct code, just off the top of my head)
create proc1 (@var1 int = null, @var2 int = 1) -- this will default @var1 to null and @var2 to 1
as
update table1
set col1 = coalesce(@var1,99), --set to 99 if no @var1 supplied
col2 = coalesce(@var2,col2) -- set to its current value if @var2 is null
Hopefully this will guide you to what you want.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
What I understood from description is you want to check whether the variables provided are null or not. If any of these is null put default value. If is that so, below is the answer:
CREATE PROC sp_TestSp (@var1 int = 0, @var2 int =0 , @var3 varchar(50) = 'Default') -- this will set default value of @var1 to 0 and @var2 to 0 and @var3 to "Default"
AS
UPDATE table1 set col1 = @var1, col2 = @var2, col3 = @var3
OR
CREATE PROC sp_TestSp (@var1 int = 0, @var2 int =0 , @var3 varchar(50) = 'Default') -- this will set default value of @var1 to 0 and @var2 to 0 and @var3 to "Default"
AS
UPDATE table1 set col1 = ISNULL(@var1, 0), col2 = ISNULL(@var2, 0), col3 = ISNULL(@var3, 'Default')
Hope this answers you question.
Umair Feroze
|
|
|
|
|
Why repeat basically what I put 7 hours ago?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks Alot
thats exactly what i wanted!
|
|
|
|
|
hello anyone tried to install SQL 2005 Express with Reporting Service on Vista Home Premium?
It's driving me nuts...[^]
I checked and double checked,
1. IIS installed, ASP/ASP.NET enabled, IIS6/IIS6 Metabase ...etc all there.
2. BEFORE install SQL 2005 Express, I made sure this patch is installed.
Update for Windows Server 2008 (KB950636)[^]
3. I made sure this is intalled AFTER SQL 2005 Express installed and BEFORE I try to reinstall to renable "Reporting Service" - Microsoft SQL Server 2005 SP2[^]
Nothing seems to work.. installer still stuck complaining IIS not installed. Perhaps home edition Vista is inherently incompatible with SQL2005 Reporting Service.
dev
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
