Default for an int parameter in SP

I'm using a stored procedure that receives one parameter namely @.EmployeeID INT

but when I want to give this parameter a default value, my SP fails.

I did it like with a varchar where it works.

--
@.Employee INT = '%'

--

Is this correct or did I use a wrong syntac/wildcard?

Greetings,
GeoffINT is a numeric datatype only you should use digits to assign defaults.
If not use VARCHAR to assign such special chars.|||Isn't there a numeric default that I can use?

actually i use the SP to do the next thing.

If I don't give a parameter I want all recoreds to be returned. When passing through the ID (as parameter tot the SP) I want only that record to be returned. So if I understand correctly you're saying there is no sucth thing as setting a default for an int-type?

Greetings,
Godofredo|||Only numerics are allowed as default for int.|||Can i define a range then?

like @.EmployeeID INT = [0-9]

so that all numbers can be received? or how precisely do I do this?

If no parameter is given I want al records returned.

Greetings,
Geoff|||CREATE PROCEDURE sp_myproc
@.emp_id int = 0
as

select * from my_table
where emp_id =
case @.emp_id when 0 then emp_id else @.emp_id end

when you call sp_myproc without specifying any parameters then all employees are retrieved.

Is that what you were asking?|||Yes indeed

Default Field Value?

Is it possible to change the default value of a field using a stored proc or query? Any hints on how to do this if it is possible would be appreciated!
Mike BJust supply the new value on the INSERT?

Or to permanently change it you need to use ALTER TABLE...I think...gotta check...go look up ALTER in Books Online (BOL)

OK?

default db permissions for account

Hi
I want to allow the windows iusr_computername account exec permissions on
user stored procs and select on views. I have 50 odd stored procs. Is there
a way of assigning permissions so that this account always has those
permissions and the permissions are automatically added when a new view or
sp is added?
I'd also like to easily transfer this to other databases. I'm sure the
answer lies in using roles or scripts or perhaps there's a fundamentally
easy way that I haven't found yet?
Thanks
AndrewHi Andrew,
There is no fundamentally easy way to assign permissions on all the stored
procedures to a user.
You can use the following script to give a user permission on all existing
stored procedures, but you have to re-run it to give permissions to newly
created stored procedures:
DECLARE @.proc_name SYSNAME
SET @.proc_name = ''
WHILE 1=1
BEGIN
SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME, 'IsMSShipped') = 0 -- Only
user stored procedures
AND ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME > @.proc_name
ORDER BY ROUTINE_NAME
)
IF @.proc_name IS NULL BREAK
EXEC ('GRANT EXECUTE ON ' + @.proc_name + ' TO MyUser')
END
You can use something similar to assign permissions on views using
inforamtion_schema.views.
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Andrew Jocelyn" <andrew.jocelyn@.REMOVETHISBITempetus.co.uk> wrote in
message news:OvwDb1ZXDHA.2424@.TK2MSFTNGP12.phx.gbl...
> Hi
> I want to allow the windows iusr_computername account exec permissions on
> user stored procs and select on views. I have 50 odd stored procs. Is
there
> a way of assigning permissions so that this account always has those
> permissions and the permissions are automatically added when a new view or
> sp is added?
> I'd also like to easily transfer this to other databases. I'm sure the
> answer lies in using roles or scripts or perhaps there's a fundamentally
> easy way that I haven't found yet?
> Thanks
> Andrew
>|||Hi thanks for that.
Just one little problem. I'm getting an error "Invalid parameter 2 specified
for object_id.". I'm afraid my attempts to debug have failed. Can you help?
Thanks again
Andrew
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:Oh%23e9saXDHA.1280@.tk2msftngp13.phx.gbl...
> Hi Andrew,
> There is no fundamentally easy way to assign permissions on all the stored
> procedures to a user.
> You can use the following script to give a user permission on all existing
> stored procedures, but you have to re-run it to give permissions to newly
> created stored procedures:
> DECLARE @.proc_name SYSNAME
> SET @.proc_name = ''
> WHILE 1=1
> BEGIN
> SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
> INFORMATION_SCHEMA.ROUTINES
> WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME, 'IsMSShipped') = 0 -- Only
> user stored procedures
> AND ROUTINE_TYPE = 'Procedure'
> AND ROUTINE_NAME > @.proc_name
> ORDER BY ROUTINE_NAME
> )
> IF @.proc_name IS NULL BREAK
> EXEC ('GRANT EXECUTE ON ' + @.proc_name + ' TO MyUser')
> END
> You can use something similar to assign permissions on views using
> inforamtion_schema.views.
>
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Andrew Jocelyn" <andrew.jocelyn@.REMOVETHISBITempetus.co.uk> wrote in
> message news:OvwDb1ZXDHA.2424@.TK2MSFTNGP12.phx.gbl...
> > Hi
> >
> > I want to allow the windows iusr_computername account exec permissions
on
> > user stored procs and select on views. I have 50 odd stored procs. Is
> there
> > a way of assigning permissions so that this account always has those
> > permissions and the permissions are automatically added when a new view
or
> > sp is added?
> >
> > I'd also like to easily transfer this to other databases. I'm sure the
> > answer lies in using roles or scripts or perhaps there's a fundamentally
> > easy way that I haven't found yet?
> >
> > Thanks
> > Andrew
> >
> >
>

Default DATE and uniqueidentifier parameters?

I have several stored procedures and to facilitate getting the output of the stored procedures we have been adding default values for all of the input parameters. This works fine with the exception of DATE and uniqueidentifier parameters. I have defined stored procedures like:

ALTER PROCEDURE [dbo].[proc_GetOrderReasonByOrderGroupId]

@.OrderGroupId uniqueidentifier = NEWID

AS

and

ALTER PROCEDURE [dbo].[proc_shippedPackages]

@.DateFrom datetime = GETDATE,

@.DateTo datetime = GETDATE

but when I execute the following

SET FMTONLY ON

exec proc_shippedPackages

SET FMTONLY OFF

I get

Msg 241, Level 16, State 1, Procedure proc_shippedPackages, Line 0

Conversion failed when converting datetime from character string.

Any suggestions? The same error occurs with setting a uniqueidentifier. I want to create a default parameter that will more of less ensure that the output is empty.

Thank you.

Kevin

You could try the example below.

Beware, though, that if you do need to explicitly set the values of the @.DateFrom and @.DateTo parameters to NULL when calling the stored procedure [as opposed to simply not providing values for these optional parameters] then the parameters will be assigned a value of GETDATE() during execution, which may or may not be what you want. If this does turn out to be a problem then you could use an arbitrary [but unlikely to be used] date as the default value and then check for that value rather than NULL when assigning the value of @.Now.

Chris

Code Snippet

CREATE PROCEDURE [dbo].[proc_shippedPackages]

@.DateFrom DATETIME = NULL,

@.DateTo DATETIME = NULL

AS

--Ensure that both variables are set to

--equal values if defaults are required.

DECLARE @.Now DATETIME

SET @.Now = GETDATE()

IF @.DateFrom IS NULL

BEGIN

SET @.DateFrom = @.Now

END

IF @.DateTo IS NULL

BEGIN

SET @.DateTo = @.Now

END

SELECT @.DateFrom, @.DateTo

GO

|||

It is not bad idea to have NON-NULL values, suppose if you need to store / pass the NULL value from your code the below code wont break.

Code Snippet

Alter PROCEDURE [dbo].[proc_GetOrderReasonByOrderGroupId]

@.OrderGroupId uniqueidentifier = 0x0

AS

Select@.OrderGroupId = Case When @.OrderGroupId = 0x0 Then NewId() Else @.OrderGroupId End

go

Alter PROCEDURE [dbo].[proc_shippedPackages]

@.DateFrom datetime = '1900-01-01 00:00:00.000',

@.DateTo datetime = '1900-01-01 00:00:00.000'

as

SET @.DateFrom = Case When @.DateFrom = '1900-01-01 00:00:00.000' Then GetDate() Else @.DateFrom End

SET @.DateTo = Case When @.DateTo = '1900-01-01 00:00:00.000' Then GetDate() Else @.DateTo End

|||GETDATE and NEWID are functions and require () after them, unlike VB. Try:

@.OrderGroupId uniqueidentifier = NEWID()

@.DateFrom datetime = GETDATE(),

@.DateTo datetime = GETDATE()

|||TPhillips -> You are wrong; SP params only support the constant/NULL value as default value.|||Yes, you are correct. Your method, mentioned earlier, is the way to fix this problem.

However, the () are still needed to execute the functions.

|||

Tom Phillips wrote:

Yes, you are correct. Your method, mentioned earlier, is the way to fix this problem.

However, the () are still needed to execute the functions.

If you check the syntax with the Sql Management Studio it complains if you add the ().

|||

Kevin,

You cannot use the NEWID() and GETDATE() functions as default values in the parameter definition.

DEFAULT value assignments must be deterministic. Non-deterministic functions are not permitted in that context.

If your intent is to make the parameters optional, use '01/01/1900' (or NULL), then in the first lines of the sproc, check the values and if = '01/01/1900' (or NULL), then set the values = getdate().

Your original attempt failed because you are setting the default values to the string constants 'GETDATE' and 'NEWID.

NEWID() and GETDATE() both require parentheses as previously mentioned.

|||

Tom Phillips wrote:

Yes, you are correct. Your method, mentioned earlier, is the way to fix this problem.

However, the () are still needed to execute the functions.

When I include the () I get:

Msg 102, Level 15, State 1, Procedure proc_GetCaseNotesByOrderGroupID, Line 4

Incorrect syntax near '('.

ALTER PROCEDURE [dbo].[proc_GetCaseNotesByOrderGroupID]

@.OrderGroupID uniqueidentifier = NEWID()

AS

|||As mentioned, you have to set the default to a "static", you cannot use a function on a default value.

What your code was doing without the () is equivalent to:

@.OrderGroupID uniqueidentifier = 'NEWID'

I assume you did not want the @.orderGroupID to be a string NEWID. I think this is a bug or at least hold over from Sybase which allows unquoted strings to be invisibly converted to a string.

The best way to do what you want is:

ALTER PROCEDURE [dbo].[proc_GetCaseNotesByOrderGroupID]

@.OrderGroupID uniqueidentifier = NULL -- or some other non-occurring number

AS

IF @.OrderGroupID IS NULL
SET @.OrderGroupID = NEWID()

sql

Default data/log path

Hi,

Is there a SQL stored procedure or command to retrieve the server's default path for data and transaction log files?

Thanks.

Part of the Profiler trace created by opening the New Database dialog:

declare @.RegPathParams sysname

declare @.Arg sysname

declare @.Param sysname

declare @.MasterPath nvarchar(512)

declare @.LogPath nvarchar(512)

declare @.ErrorLogPath nvarchar(512)

declare @.n int

select @.n=0

select @.RegPathParams=N'Software\Microsoft\MSSQLServer\MSSQLServer'+'\Parameters'

select @.Param='dummy'

while(not @.Param is null)

begin

select @.Param=null

select @.Arg='SqlArg'+convert(nvarchar,@.n)

exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', @.RegPathParams, @.Arg, @.Param OUTPUT

if(@.Param like '-d%')

begin

select @.Param=substring(@.Param, 3, 255)

select @.MasterPath=substring(@.Param, 1, len(@.Param) - charindex('\', reverse(@.Param)))

end

else if(@.Param like '-l%')

begin

select @.Param=substring(@.Param, 3, 255)

select @.LogPath=substring(@.Param, 1, len(@.Param) - charindex('\', reverse(@.Param)))

end

else if(@.Param like '-e%')

begin

select @.Param=substring(@.Param, 3, 255)

select @.ErrorLogPath=substring(@.Param, 1, len(@.Param) - charindex('\', reverse(@.Param)))

end

select @.n=@.n+1

end

SELECT

@.MasterPath AS [MasterDBPath],

@.LogPath AS [MasterDBLogPath]

Decrypting Stored Procedures

We have a product that compares databases and generates the scripts to
synchronize them. Need to be able to compare encrypted Stored Procedures - is
there an official route for obtaining the information we need?
xSQL wrote:
> We have a product that compares databases and generates the scripts to
> synchronize them. Need to be able to compare encrypted Stored
> Procedures - is there an official route for obtaining the information
> we need?
Nothing official. They are encrypted, so the procedure text is not
available through normal means. You can decrypt the stored procedures
using some posted code available on the internet, but this will change
the system tables where the encrypted text is stored. There's no way to
read the encrypted text and decrypt on the fly as far as I know using a
schema comparison tool. Decrypting the procedures could be a violation
of any of a number of US and international laws if you are not the
owner.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Decrypting Password of SysxLogins Table - SQL Database.

Hi,

Is there any way of decrypting password value stored in sysxlogins table of SQL database?

Thx in Adv

This is a SQL Server 2000 table that is no longer available in SQL Server 2005. The password value normally contains a SHA1 hash of the password (unless the password is for a login created in a previous version and maintained through upgrade to SQL Server 2000). If the value is NULL and the login is a SQL login, it means that the password is empty. Otherwise, to determine the password, you would have to do a brute force attack on the hash. If the password is weak, a brute force attack will be quite successful, so it's very important to have strong passwords.

In SQL Server 2005, password strength can be enforced on Windows 2003 systems to follow the Windows password policy settings. Also, these password hashes can only be seen by a sysadmin now. Furthermore, empty passwords cannot be as easily identified, they have a hash as well rather than showing up as NULL.

Thanks

Laurentiu

sql

Decrypting Encrypted Views/Sp/Functions.....?

Hi all,
As all of you are aware you can Encrypt your Triggers/Stored Procedures/Views And Functions
in Sql Server with "WITH ENCRYPTION" clause.recently i came across a Stored procedure on the Net that could reverse and decrypt all Encrypted objects.i personally tested it and it really works.That's fine (of course for some body)
Now i want to know is it a Known Bug for Sql Server 2000 and is there a permanent solution for Encrypting mentioned objects.

Thanks in advance.
Best Regards.

Yes. No.|||

There are huge enhancements with Encrption/Decrption in SQL2005, you may take a look at this article:

http://www.sqlservercentral.com/columnists/mcoles/sql2005symmetricencryption.asp

Decrypting and Encrypted Stored Procedure

Hello,
In SQL 2000,
I have created a Stored Procedure as follows,

Code Snippet

CREATE PROCEDURE MyTest
WITH RECOMPILE, ENCRYPTION
AS
Select * From Customer

Then after this when i run this sp it giving me the perfect results wht i want, BUT when i want to change something in sp then for I am using the below line of code.

Code Snippet

sp_helptext mytest

But its displaying me that this sp is encrypted so you can't see the details and when i am trying to see trhe code of this sp from enterprise manager then also its not displaying me the details and giving me the same error,
So i want to ask that if there is a functionality of enrypting the sp code then is there any functionality for decrypting the Stored Procedure also,
or not,
If yes then wht it is and if NO then wht will be the alternative way for this,
?

You won’t retrieve back the source using sp_helptext /SMO, when you say WITH ENCRYPT.

You have to maintain your procedure source (like in File system or VSS). The encryption is very useful when you launch a product along with your database to public. So they can see the table schema but they can’t change or edit or view your programmability source code.

DecryptByCert performance

I have bunch of encrypted rows in the table and have stored procedure to select those rows.
It looks like this
SELECT CAST(DecryptByCert(Cert_ID('CertId'), field1) AS VARCHAR) AS f1,
CAST(DecryptByCert(Cert_ID('CertId'), field2) AS VARCHAR) AS f2,
CAST(DecryptByCert(Cert_ID('CertId'), field3) AS VARCHAR(255)) AS f3
FROM [table]

This stored procedure takes really long time even with hundreds of rows, so I suspect that I do something wrong. Is there any way to optimize this stored procedure?

Encryption/decryption by an asymmetric key (i.e. certificate) is much slower than when using a symmetric key.The recommended way to encrypt data is by using a symmetric key (or set of keys if you prefer) for protecting the data (i.e. AES or 3DES key), and protect these key using the certificate.

Another limitation you should consider is that asymmetric key encryption in SQL Server 2005 is limited to only one block of data. This means that the maximum amount of plaintext you can encrypt is limited by the modulus of the private key. In the case of RSA 1024 (in case you are using SQL Server 2005-generated certificates), the maximum plaintext is 117 bytes.

I am also including an additional thread from this forum that talk about this topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=114314&SiteID=1

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Decode Base64 to Image!

Hi,

I have a xml string which is consist of some images encoded in base64; I have to extract these images in a stored procedure and save them in some tables.

The problem is that I can't decode this base64s to images. Is there a way to do it? (I use SQL Server 2005 Enterprise)

Thanx

Convert.FromBase64String
You probably want to take a look at that. You could possibly create a memory stream from the byte array and then create a bitmap from the stream.

|||

Thank you for your help but as I've mentioned I have to extract images in a stored procedure, so I have to decode them in it too.

Sorry for my late reply.

|||hwat about usin a CLR procedure if you have SQL2k5 already. YOu could use the mentioned class in the last post. That should work for you.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

declaring a sql query to a variable...

Hello all! After I declar a variable how would I set the result of a sql query to the variable so i can utilize it further in my stored procedure?

-Thanks,
Rich

declare @.what varchar(2)

Code Snippet

select @.what = targetColumn

from targetTable

Where testKey = 'Whatever'

-- or the SET alternative:

Code Snippet

set @.what

= ( select taretColumn

from targetTable

where testKey = 'Whatever'

)

Give a look at SET and SELECT in books online.

|||

Kent Waldrop Se07 wrote:

declare @.what varchar(2)

Code Snippet

select @.what = targetColumn

from targetTable

Where testKey = 'Whatever'

-- or the SET alternative:

Code Snippet

set @.what

= ( select taretColumn

from targetTable

where testKey = 'Whatever'

)

Give a look at SET and SELECT in books online.

Thanks! When you mentioned to have a look at SET and SELECT in books online... are the books free or do i need to purchase them?

-Thanks,
Rich
|||

Free. As a download:

http://www.microsoft.com/downloads/results.aspx?pocId=&freetext=sql%20server%20books%20online&DisplayLang=en

As a webpage:

http://msdn2.microsoft.com/en-us/library/bb545450.aspx

declare variables

New to stored procedures. Is it necessary to place a default value into a
variable at the time you declare it?
SAMPLE: “ @.Sec int = 100”
Can @.Sec just be declared?It can just be declared. It will default to NULL:
DECLARE @.SEC INT
SELECT @.SEC
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:10D3C4D8-9FD7-47E0-B77E-DD1C1A4EE8CA@.microsoft.com...
> New to stored procedures. Is it necessary to place a default value into a
> variable at the time you declare it?
> SAMPLE: " @.Sec int = 100"
> Can @.Sec just be declared?
>|||No, it is not necessary, but until you put a value into it, it's value will
be Null. This can bite you if it's a char() or varChar() because by defaul
t
(there's a setting to change this, but don't use it) the nulls propagate whe
n
you concatenate them... i.e.,
null + 'dsasdasd' is null
"Rich" wrote:

> New to stored procedures. Is it necessary to place a default value into a
> variable at the time you declare it?
> SAMPLE: “ @.Sec int = 100”
> Can @.Sec just be declared?
>|||"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:548AE03F-A9DD-441E-98AD-5F7416492EB5@.microsoft.com...
> No, it is not necessary, but until you put a value into it, it's value
will
> be Null. This can bite you if it's a char() or varChar() because by
default
> (there's a setting to change this, but don't use it) the nulls propagate
when
> you concatenate them... i.e.,
> null + 'dsasdasd' is null
It's no different with numeric types:
SELECT CONVERT(INT, NULL) + 1
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Ok, that is good but here is a second part to the question. If I place a
default value in the variable but then at run time, I have a agent that call
s
the stored procedure and pushes a value to it, will the value pushed in
always take priority over the default value?
"Rich" wrote:

> New to stored procedures. Is it necessary to place a default value into a
> variable at the time you declare it?
> SAMPLE: “ @.Sec int = 100”
> Can @.Sec just be declared?
>|||Are you talking about variables within the procedure, or paramaterss to the
procedure?
Variables within the proc, at any given point in code, will have whatever
value was last assigned (if any). Just like any other language. E.g:
DECLARE @.variable INT
-- @.variable is NULL
SET @.variable = 1
-- @.variable = 1
SET @.variable = 2
-- @.variable = 2
Parameters with a default value are different - if you pass the param to the
proc it will have the value you passed (including NULL); if you do not pass
the param it will have the default value. E.g:
CREATE PROC foo (@.i INT = 0) AS
BEGIN
SELECT @.i AS i
END
EXEC foo -- returns 0
EXEC foo @.i = 1 -- returns 1
EXEC foo @.i = NULL -- returns NULL
"Rich" wrote:
> Ok, that is good but here is a second part to the question. If I place a
> default value in the variable but then at run time, I have a agent that ca
lls
> the stored procedure and pushes a value to it, will the value pushed in
> always take priority over the default value?
> "Rich" wrote:
>|||Yes, If the parameter declaration in the Stored Proc has a default value,
and you nevertheless pass in a value, the passed in value will always take
pre3cedence over the default value.
This is true even When the Passed in value is Null, and teh default value
is somethiong other than Null...
"Rich" wrote:
> Ok, that is good but here is a second part to the question. If I place a
> default value in the variable but then at run time, I have a agent that ca
lls
> the stored procedure and pushes a value to it, will the value pushed in
> always take priority over the default value?
> "Rich" wrote:
>|||Hello CB,
perfect, I kind of tested that and found it to be true, I just wanted to
hear it from another programmer!
:)
"CBretana" wrote:
> Yes, If the parameter declaration in the Stored Proc has a default value,
> and you nevertheless pass in a value, the passed in value will always take
> pre3cedence over the default value.
> This is true even When the Passed in value is Null, and teh default value
> is somethiong other than Null...
> "Rich" wrote:
>

Declare cursor for execute stored_procedure

Hello,

I am using SQL 2005 and i would like to create a cursor from executing a stored procedure (dbo.SP_getDate @.Variable).
Something like this:

DECLARE Cursor1 CURSOR FOR EXECUTE dbo.SP_getDate @.Variable

i get an error saying "incorrect syntax near the keyword 'EXECUTE'."
cannot get rid of the error. what am i doing wrong?
(i am trying to avoid using #tempTbl to store the results of the execute first and then doing a select on the #tempTbl)

Not sure if i am doing this right all together.
any help would be greatly appreciate.See if this helps. It is not good practice to concatenate user entries into a SQL string, so be careful not to risk SQL Injection.

create proc SP_getDate (

@.v int

) as

SET NOCOUNT ON

SELECT TOP (@.v) HireDate

FROM AdventureWorks.HumanResources.Employee

ORDER BY EmployeeID

go

DECLARE @.sql NVARCHAR(1000)

DECLARE @.Variable int

SET @.Variable = 13

SET @.sql = '

DECLARE Cursor1 CURSOR FOR

SELECT HireDate

FROM OPENQUERY([SK8400\YUK], ''exec AdventureWorks.dbo.SP_getDate @.v'')'

SET @.sql = REPLACE(@.sql,'@.v',@.Variable)

exec (@.sql)

go

declare @.d datetime

open Cursor1

toploop:

fetch from Cursor1 into @.d

while @.@.fetch_status = 0 begin

print @.d

goto toploop

end

close Cursor1

deallocate Cursor1

go

drop proc SP_getDate

-- Steve Kass

-- Drew University

-- http://www.stevekass.com

Yassi@.discussions.microsoft.com wrote:

> Hello,

>

> I am using SQL 2005 and i would like to create a cursor from executing a

> stored procedure (dbo.SP_getDate @.Variable).

> Something like this:

>

> DECLARE Cursor1 CURSOR FOR EXECUTE dbo.SP_getDate @.Variable

>

> i get an error saying "incorrect syntax near the keyword 'EXECUTE'."

> cannot get rid of the error. what am i doing wrong?

> (i am trying to avoid using #tempTbl to store the results of the execute

> first and then doing a select on the #tempTbl)

>

> Not sure if i am doing this right all together.

> any help would be greatly appreciate.

>

>

Declare @var?

I am trying to get a grasp on the Sql Stored procedures it seems i dont really understnad what DECLARE @.Date DateTime means? I mean i think it means that i am just declaring a varible name Date that will hold a DateTime Value? is that correct or is it more to it?

CREATE PROCEDURE dbo.Tracking_GetStatus
AS
DECLARE @.Date DateTime
DECLARE @.Begining DateTime
DECLARE @.Ending DateTime
SET @.Date = GETDATE()
SET @.Begining = DATEADD(ss,(DATEPART(ss,@.Date)*-1),
DATEADD(mi,(DATEPART(mi,@.Date)*-1),
DATEADD(hh,(DATEPART(hh,@.Date)*-1),@.Date)))
SET @.Ending = DATEADD(ss,-1,
DATEADD(dd,1,DATEADD(ss,(DATEPART(ss,@.Date)*-1),
DATEADD(mi,(DATEPART(mi,@.Date)*-1),
DATEADD(hh,(DATEPART(hh,@.Date)*-1),@.Date)))))

SELECT
Vehicl,
UpdateTi
XCoord,
YCoord,
Status
FROM Track
WHERE UpdateTime >= @.Begining
AND UpdateTime <= @.Ending
RETURN

GO

You are correct. The DECLARE statement declares variables in T-SQL. All variables MUST be declared, and they only are only scoped (available) to the procedure or batch in which they are declared. The variable will initially contain NULL as it's value and needs to be initialized for use, but you did that with each of your SET statements.

Everything looks fine. Are you having a problem?

Decimal.MinValue throw OverflowException

I'm using Decimal.MinValue in SqlParameter( SqlDbType.Decimal ) to execute a stored procedure.

But, I receive this stack trace exception:

System.OverflowException: Conversion overflows.
at System.Data.SqlTypes.SqlDecimal.ToDecimal()
at System.Data.SqlTypes.SqlDecimal.get_Value()
at System.Data.SqlClient.TdsParser.AdjustDecimalScale(Decimal value, Int32 newScale)
at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

I have changed Decimal.MinValue to Decimal.Zero to resolve my trouble. But, Why Decimal.MinValue throw OverflowException? Isn't Decimal.MinValue valid SqlDbType.Decimal type?

From the online help:

The value of this constant is negative 79,228,162,514,264,337,593,543,950,335.

How many digits did you set the decimal database field to accept?

|||

Hi,

Please check if the decimal scale and precision are set correctly in both .NET and database.

The should be match, so the data conversion can work properly.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Decimal values

Ho do I pass a real value containing decimals to my stored procedure,
my values get truncated after the decimal..
any help!!!!!!!!!!!!!!!!!!!Can you post a repro on this? How is the parameter defined in the stored pro
cedure, for instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<aroraamit81@.gmail.com> wrote in message
news:1138208844.360294.126400@.g47g2000cwa.googlegroups.com...
> Ho do I pass a real value containing decimals to my stored procedure,
> my values get truncated after the decimal..
> any help!!!!!!!!!!!!!!!!!!!
>

Decimal values

Ho do I pass a real value containing decimals to my stored procedure,
my values get truncated after the decimal..
any help!!!!!!!!!!!!!!!!!!!
Can you post a repro on this? How is the parameter defined in the stored procedure, for instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<aroraamit81@.gmail.com> wrote in message
news:1138208844.360294.126400@.g47g2000cwa.googlegr oups.com...
> Ho do I pass a real value containing decimals to my stored procedure,
> my values get truncated after the decimal..
> any help!!!!!!!!!!!!!!!!!!!
>

Decimal values

Ho do I pass a real value containing decimals to my stored procedure,
my values get truncated after the decimal..
any help!!!!!!!!!!!!!!!!!!!Can you post a repro on this? How is the parameter defined in the stored procedure, for instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<aroraamit81@.gmail.com> wrote in message
news:1138208844.360294.126400@.g47g2000cwa.googlegroups.com...
> Ho do I pass a real value containing decimals to my stored procedure,
> my values get truncated after the decimal..
> any help!!!!!!!!!!!!!!!!!!!
>

decimal return on stored procedure.

There's something thats driving me nuts. I have a stored procedure that
returns the result of dividing two different stored prodecures. Here's the
code in QA:
declare @.trhhp decimal(9, 2)
declare @.acp decimal(9, 2)
exec @.trhhp = [clas0_TeleFlash].[dbo].[FIRST_SP]]
exec @.acp = [clas0_TeleFlash].[dbo].[SECOND_SP]
select @.acp / @.trhhp
.003551038825
(1 row(s) affected)
...now when I place it into a stored procedure:
CREATE PROCEDURE [dbo].[PERCERNT_MKT_PENETRATION] AS
declare @.trhhp decimal
declare @.acp decimal
exec @.trhhp = [clas0_TeleFlash].[dbo].[FIRST_SP]
exec @.acp = [clas0_TeleFlash].[dbo].[SECOND_SP]
return (@.acp / @.trhhp)
GO
...and then execute the stored procedure, I seems to round the value to
zero. Any idea why this might be? I'm using decimal values when I can...
DECLARE @.RC decimal
-- Set parameter values
EXEC @.RC = [clas0_TeleFlash].[dbo].[PERCERNT_TELE_PENETRATION]
select @.rc
0
(1 row(s) affected)
Thanks for any help!
-- CaseyHow would I set up the output paramater?
I tried changing the sp...
CREATE PROCEDURE [dbo].[PERCERNT_TELE_PENETRATION]
@.result decimal OUTPUT
AS
declare @.trhhp decimal
declare @.acp decimal
exec @.trhhp = [clas0_TeleFlash].[dbo].[TELE_READY_HHP]
exec @.acp = [clas0_TeleFlash].[dbo].[ACTIVE_CUSTOMERS_PRI]
SET @.result = (select @.acp / @.trhhp)
return
GO
...and then tried retriving the value in QA:
DECLARE @.result decimal
-- Set parameter values
EXEC [clas0_TeleFlash].[dbo].[PERCERNT_TELE_PENETRATION] @.result output
select @.result
... still returns 0... I imagine I'm doing something wrong...
"Nigel Rivett" wrote:
> A return value is always an integer - you need to use an output parameter
(or
> result set).
> "Casey" wrote:
>|||ah! That would be the problem then. How to I set an output pramater? I tried
recreating the SP:
CREATE PROCEDURE [dbo].[PERCERNT_TELE_PENETRATION]
@.result decimal OUTPUT
AS
declare @.trhhp decimal
declare @.acp decimal
exec @.trhhp = [clas0_TeleFlash].[dbo].[TELE_READY_HHP]
exec @.acp = [clas0_TeleFlash].[dbo].[ACTIVE_CUSTOMERS_PRI]
SET @.result = (select @.acp / @.trhhp)
return
GO
Then, when I try to grab that value out of the sp, I still only get zero...
DECLARE @.result decimal
-- Set parameter values
EXEC [clas0_TeleFlash].[dbo].[PERCERNT_TELE_PENETRATION] @.result output
select @.result
I'm doing something wrong...
"KH" wrote:
> The return value of an SP is a success/failure indicator. As Nigel said us
e a
> recordset or output param.
>
> "Casey" wrote:
>|||> @.result decimal OUTPUT
Try setting scale / precision for your decimal parameter.|||I thought's that's what I did...
"Aaron Bertrand [SQL Server MVP]" wrote:

> Try setting scale / precision for your decimal parameter.
>
>|||Be careful with the decimal data type. Scale is 0 by default => specifying
decimal without precision and scale is equal to decimal(18, 0). And that's
not equal to decimal(9, 2) as you declared it in one of the cases.
ML|||>I thought's that's what I did...
Maybe you're not sure what precision/scale are?
You have:
@.result decimal OUTPUT
AS
declare @.trhhp decimal
declare @.acp decimal
You should try
@.result DECIMAL(9,2) OUTPUT
AS
DECLARE @.trhhp DECIMAL(9,2),
@.acp DECIMAL(9,2)
What does trhhp mean? I certainly can't pronounce it, never mind decipher
what it might stand for...
A