Default date problem

Hi,

Good Day!

In my sproc, I m trying to set a default value for a parameter, but it's sending me an error. It seems like having probs with brackets!

Code Snippet

@.Date DateTime = GetDate(),

Code Snippet

Msg 102, Level 15, State 1, Procedure usp_Receive_Add, Line 8
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Procedure usp_Receive_Add, Line 40
Must declare the scalar variable "@.ProductID".

Please tell me what's the prob! I m trying to set todays date to the parameter if nothing was supplied in the @.Date parameter.

Regards

Kapalic

Use the following logic to set the current date..

Code Snippet

Create proc MyProc
(
@.Date datetime = '1900-01-01'
)
as
Begin
Select @.Date = Case When @.Date <> '1900-01-01' Then @.Date Else Getdate() End
Select @.Date
End

go

Exec MyProc '2/2/2007'
Exec MyProc

|||How about setting default value of @.date to NULL and set it to GetDate() in store procedure body? I just wandering 1900-1-1 is valid value. here is valid value of DateTime according SQL Server 2005 document.

datetime

January 1, 1753, through December 31, 9999

|||

Yes.. You can do it with NULL.

Suppose if you want to store the explicit null value on your table then this logic wont work. RITE?

So we are setting some default value which we are assuming that it never passed from our UI.

1900-01-01 is valid value only. it is with in the given range Buddy.. It is a typical sql coders starting value [Cast(0 as Datetime)]

|||

In a variable declaration, you can set a variable to a constant, e.g., a value.

However, you cannot set it to the results of a function. Getdate() is a function.

As suggested, if you wish to make the parameter optional, set a default value of '01/01/1900', and then if you wish to set it to the current date/time, after entering the procedure code (after 'AS'), set the parameter = getdate().

Decrypt sproc returning NULL to non DBO.

I'm still having issues with this despite my attempts to resolve. I even
have "with exec as dbo" in my sproc, and and "exec as dbo" in my execution,
but still the encrypted data returns nulls when I exec as a user other than
DBO. Below is precisely what I have done. All ideas are welcomed.

TIA, ChrisR

--If there is no master key, create one now

IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478d Dkjdahflkujaslekjg5k3fd117
r$$#1946kcj$n44ncjhdlj'
GO

CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO

CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO

USE [AdventureWorks];
GO

-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO

-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO

-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO

-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.

alter procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO

/*works for me, shows the decrypted data*/

exec getDecryptedIDNumber

USE [master]
GO

CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [AdventureWorks]
GO

CREATE USER [test] FOR LOGIN [test]
GO

use [AdventureWorks]
GO

GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO

GRANT IMPERSONATE ON USER:: dbo TO test;
GO

/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber

/*This returns NULL values where it should show the decrypted data*/I have made some changes to the scripts, but the outcome is the same. Everything needed is below, so all ideas are welcomed. Also, please make sure to use these scripts, not the last ones.

USE [AdventureWorks];
GO

IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'vato'
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'vato'

CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO

CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO

-- Create a column in which to store the encrypted data
ALTER TABLE HumanResources.Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO

-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO

-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO

-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.

create procedure getDecryptedIDNumber
with exec as owner
as
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM HumanResources.Employee;
GO

/*works for me, shows the decrypted data*/

exec getDecryptedIDNumber

USE [master]
GO

CREATE LOGIN [test] WITH PASSWORD=N'test',
DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [AdventureWorks]
GO

CREATE USER [test] FOR LOGIN [test]
GO

use [AdventureWorks]
GO

GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test]
GO

GRANT IMPERSONATE ON USER:: dbo TO test;
GO

/*Now, open up a "file/new/DB Engine Query" and login with the test login*/
exec as user = 'dbo'
exec getDecryptedIDNumber

/*This returns NULL values where it should show the decrypted data*/

Declaring Large Variables In SProc

Hello
I am trying to use the OPENXML command within a sproc to parse an xml
document and save the data to a table.
The xml document is saved in a different table in a field with data type
TEXT.
To parse the document, I first have to prepare it by calling ...
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
... where @.doc is the variable holding the xml document
I am trying to load the xml document into the local @.doc variable by ....
SELECT @.doc = xml_data FROM XMLTABLE
My problem is the xml document is 12-13k long so I can't declare the @.doc
variable as a varchar because a varchar can only be declared to a maximum of
8000 bytes and I cant declare it as a text datatype because local variables
can't be declare as text.
So.....what do I do?
Regards
Peter
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--Peter,
Take a look at the following in BOL
TEXTPTR, WRITETEXT and UPDATETEXT.
You will first need to get a pointer to the Text field and then use the
other two functions to manipulate the content like so
declare @.ptr binary(16)
SELECT @.ptr = TEXTPTR(xml_data) FROM XMLTABLE
kevin
"Peter" wrote:

> Hello
> I am trying to use the OPENXML command within a sproc to parse an xml
> document and save the data to a table.
> The xml document is saved in a different table in a field with data type
> TEXT.
> To parse the document, I first have to prepare it by calling ...
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> ... where @.doc is the variable holding the xml document
> I am trying to load the xml document into the local @.doc variable by ....
> SELECT @.doc = xml_data FROM XMLTABLE
> My problem is the xml document is 12-13k long so I can't declare the @.doc
> variable as a varchar because a varchar can only be declared to a maximum
of
> 8000 bytes and I cant declare it as a text datatype because local variable
s
> can't be declare as text.
> So.....what do I do?
> Regards
> Peter
>
> --== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet New
s==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ N
ewsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
-
>|||Thanks Kevin.
I have looked up what you suggested but still can't work out how it helps me
(it is 6.30am and I havn't been to bed yet).
I have retieved the pointer as you outlined but then what do I do with it?
How do I pass the contents of that pointer to the sp_xml_preparedocument
procedure?
Peter
"kevin" <kevin@.discussions.microsoft.com> wrote in message
news:6D465FA3-9C96-4D24-A32A-289ED73DD7E7@.microsoft.com...
> Peter,
> Take a look at the following in BOL
> TEXTPTR, WRITETEXT and UPDATETEXT.
> You will first need to get a pointer to the Text field and then use the
> other two functions to manipulate the content like so
> declare @.ptr binary(16)
> SELECT @.ptr = TEXTPTR(xml_data) FROM XMLTABLE
> kevin
> "Peter" wrote:
>
>
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Peter,
Excuse me if I double posted.
You can pass the pointer into the proc and then use the UPDATETEXT function
to manipulate the text in the xml. I would personally pull that text back t
o
code (c#/Java/VB/C++/etc) and manipulate it there, but I am assuming you hav
e
some plan for that. the following is an example
******************************
--the table and some data
CREATE TABLE dbo.kevtest (
[int] bigint IDENTITY (1, 1) NOT NULL ,
myname varchar (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
mytext text COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
INSERT INTO dbo.kevtest(myname,mytext)('KEVIN','this is my text')
--the sp
ALTER proc dbo.usp_testptr
@.p binary(16)
as
updatetext dbo.kevtest.mytext @.p null 0 ' How you like me now?'
--the TSQL
declare @.ptr binary(16)
select * from dbo.kevtest
select @.ptr = TEXTPTR(mytext) from dbo.kevtest
EXEC dbo.usp_testptr @.ptr
select * from dbo.kevtest
******************************
kevin
"Peter" wrote:

> Thanks Kevin.
> I have looked up what you suggested but still can't work out how it helps
me
> (it is 6.30am and I havn't been to bed yet).
> I have retieved the pointer as you outlined but then what do I do with it?
> How do I pass the contents of that pointer to the sp_xml_preparedocument
> procedure?
> Peter
> "kevin" <kevin@.discussions.microsoft.com> wrote in message
> news:6D465FA3-9C96-4D24-A32A-289ED73DD7E7@.microsoft.com...
>
> --== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet New
s==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ N
ewsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
-
>|||That should be
INSERT INTO dbo.kevtest(myname,mytext) VALUES('KEVIN','this is my text')
"kevin" wrote:
> Peter,
> Excuse me if I double posted.
> You can pass the pointer into the proc and then use the UPDATETEXT functio
n
> to manipulate the text in the xml. I would personally pull that text back
to
> code (c#/Java/VB/C++/etc) and manipulate it there, but I am assuming you h
ave
> some plan for that. the following is an example
> ******************************
> --the table and some data
> CREATE TABLE dbo.kevtest (
> [int] bigint IDENTITY (1, 1) NOT NULL ,
> myname varchar (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> mytext text COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> INSERT INTO dbo.kevtest(myname,mytext)('KEVIN','this is my text')
> --the sp
> ALTER proc dbo.usp_testptr
> @.p binary(16)
> as
> updatetext dbo.kevtest.mytext @.p null 0 ' How you like me now?'
> --the TSQL
> declare @.ptr binary(16)
> select * from dbo.kevtest
> select @.ptr = TEXTPTR(mytext) from dbo.kevtest
>
> EXEC dbo.usp_testptr @.ptr
>
> select * from dbo.kevtest
> ******************************
> kevin
>
> "Peter" wrote:
>|||Thanks Kevin
I appreciate your time and effort here but the code you have supplied works
because the sproc you have created (usp_testptr) expects a pointer (or at
least a binary(16) value).
However, the sproc I need to use (sp_xml_preparedocument) expects a char,
varchar or text field so I cannot pass the pointer to it.
I may be totally and not seeing the forest for the trees here but I
still cant get you code to work in my circumstance.
Kind Regards
Peter
"kevin" <kevin@.discussions.microsoft.com> wrote in message
news:4152B5ED-54F8-4341-8539-C5BE18484A2E@.microsoft.com...
> That should be
> INSERT INTO dbo.kevtest(myname,mytext) VALUES('KEVIN','this is my text')
>
> "kevin" wrote:
>
>
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Peter,
[forum gurus please correct me if I am wrong!]
My suggestion is an alternative. Unless you can be certain that the xml
string will be less than 8000 characters you can't pass it to that sproc.
What I am suggesting is a way you can tell the sproc how to find the text
containing the xml so that it can manipulate it.
There are system stored procedures that you can use to read from a file, but
even then the sproc will still be limited to holding the xml in a
varchar(8000) as you can't create text variable in a sproc... as you already
know.
Why can't you deal with the xml in code?
Has this sproc every worked as you expect it to, or is it in development?
Kevin
"Peter" wrote:

> Thanks Kevin
> I appreciate your time and effort here but the code you have supplied work
s
> because the sproc you have created (usp_testptr) expects a pointer (or at
> least a binary(16) value).
> However, the sproc I need to use (sp_xml_preparedocument) expects a char,
> varchar or text field so I cannot pass the pointer to it.
> I may be totally and not seeing the forest for the trees here but
I
> still cant get you code to work in my circumstance.
> Kind Regards
> Peter
>
> "kevin" <kevin@.discussions.microsoft.com> wrote in message
> news:4152B5ED-54F8-4341-8539-C5BE18484A2E@.microsoft.com...
>
> --== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet New
s==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ N
ewsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
-
>

Declaring a tablename in a sproc as a parameter

Hi

Can someone please shed some light as to how this can be done.

With the below sql statement as you can see I don't want to declare the table name but would rather the table name be passed in via a parameter. I've tried declaring it as a varchar parameter but it doesnt seem to like it if I don't add a valid table name. Any ideas how this can be done. Thanks.

select * from @.tableName where condition = condition

exec ('select * from ' + @.tablename)

|||

Tried doing it with the statement and it doesnt work. Obviously you have to declare variable and set it, here's how I've done it below:

declare @.tablename varchar;
set @.tablename = 'tablename'
exec('select * from' + @.tablename)

I get an error saying invalid object name 't'

|||

try

declare @.tablename varchar (100)

|||

Yes, varchar == varchar(1).

Also you are missing a space after the FROM keyword: exec('select * from' + @.tablename), but, given the error message, this might just be the sample you have posted...

-LV

|||

I've got it working guys, thanks final code is:

declare @.tablename varchar(100);
set @.tablename = 'tbl_name'
exec('select * from ' + @.tablename)