How to Link? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to Link?

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-08 : 18:09:40

I have a code snippet for generating random password:

declare @password varchar(8)
set @password=''
select @password=@password+char(n) from
(
	select top 8 number  as n from master..spt_values 
	where type='p' and number between 48 and 122
	order by newid()
) as t
select @password as 'Password'

Now, I have a column name: st_password in table: student_details in MS SQL 2000.

I want to keep this random password generate code in my database as procedure or function and that should be directly linked with st_password column.
How should I do this?
I am very much new to MS SQL, so please help me!

Daipayan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-08 : 19:36:47

CREATE PROC SomeProcName
(@password varchar(8) OUTPUT)

set @password=''
select @password=@password+char(n) from
(
select top 8 number as n from master..spt_values
where type='p' and number between 48 and 122
order by newid()
) as t
GO

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-08 : 21:12:13

i think its better to make it a function


CREATE FUNCTION GenerateRandomPassword
()
RETURNS varchar(8) 
AS
declare @password varchar(8)
set @password=''
select @password=@password+char(n) from
(
select top 8 number as n from master..spt_values 
where type='p' and number between 48 and 122
order by newid()
) as t
return @password
GO

then make the column as calculated column like


CREATE TABLE student_details
(
....,
st_password AS dbo.GenerateRandomPassword()
)


or use an update

UPDATE student_details
SET st_password =dbo.GenerateRandomPassword()

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-02-08 : 22:51:50

quote:
Originally posted by visakh16

i think its better to make it a function...

You can't use newid within a function.

CODO ERGO SUM

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-09 : 02:14:52

quote:

Originally posted by visakh16

i think its better to make it a function


CREATE FUNCTION GenerateRandomPassword
()
RETURNS varchar(8) 
AS
declare @password varchar(8)
set @password=''
select @password=@password+char(n) from
(
select top 8 number as n from master..spt_values 
where type='p' and number between 48 and 122
order by newid()
) as t
return @password
GO

then make the column as calculated column like


CREATE TABLE student_details
(
....,
st_password AS dbo.GenerateRandomPassword()
)


or use an update

UPDATE student_details
SET st_password =dbo.GenerateRandomPassword()

While creating the function, am getting following errors:

Server: Msg 156, Level 15, State 1, Procedure GenerateRandomPassword, Line 5
Incorrect syntax near the keyword 'declare'.
Server: Msg 443, Level 16, State 1, Procedure GenerateRandomPassword, Line 11
Invalid use of 'newid' within a function.

Daipayan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 04:25:25

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by visakh16

i think its better to make it a function...

You can't use newid within a function.

CODO ERGO SUM

sorry...i didnt notice that newid()
yup...its not possible to use it within function

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-09 : 05:10:38

quote:

sorry...i didnt notice that newid()
yup...its not possible to use it within function

Is there any other possibilities??

Daipayan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 05:26:01

the other possibility is what tara suggested. where you make it procedure and call it to update each record in table. othewise, include the logic inline with an update statement

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-09 : 10:10:59

quote:
Originally posted by tkizer

CREATE PROC SomeProcName
(@password varchar(8) OUTPUT)

set @password=''
select @password=@password+char(n) from
(
select top 8 number as n from master..spt_values
where type='p' and number between 48 and 122
order by newid()
) as t
GO

Tara Kizer

Am getting this error......while executing the Procedure

Server: Msg 156, Level 15, State 1, Procedure SomeProcName, Line 4
Incorrect syntax near the keyword 'set'.

Daipayan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 10:14:25

[code]
CREATE PROC SomeProcName
(@password varchar(8) OUTPUT)
AS
set @password=''
select @password=@password+char(n) from
(
select top 8 number as n from master..spt_values
where type='p' and number between 48 and 122
order by newid()
) as t
GO
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-09 : 10:15:42

CREATE PROC SomeProcName
(@password varchar(8) OUTPUT)
AS
set @password=''
select @password=@password+char(n) from
(
select top 8 number as n from master..spt_values
where type='p' and number between 48 and 122
order by newid()
) as t
GO

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-09 : 10:16:08

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 10:16:18

quote:
Originally posted by madhivanan

CREATE PROC SomeProcName
(@password varchar(8) OUTPUT)
AS
set @password=''
select @password=@password+char(n) from
(
select top 8 number as n from master..spt_values
where type='p' and number between 48 and 122
order by newid()
) as t
GO

Madhivanan

Failing to plan is Planning to fail

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-09 : 11:03:01

I stored the procedure in the name of RandomProcedure. Now, how should I link with st_password column in student_details table.

I know, am asking very silly question, but I am very new to MS SQL 2000, just started learning this DBMS, so please guide me.

@visakh16
Also, Sir, u given me a solution, how to generate Auto ID year wise in following link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=119422

The ID is generateing as per my needs, but I cannot make this ID column linked with others columns in another table, please help me in this part also.

Daipayan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 11:15:32

just use something like:-


DECLARE @pwd varchar(8),@ID int

SELECT @ID=MIN(PK)
FROM student_details

WHILE @ID ISNOT NULL
BEGIN
EXEC SomeProcName @pwd OUTPUT

UPDATE student_details
set st_password = @pwd
WHERE PK=@ID

SELECT @ID=MIN(PK)
FROM student_details
WHERE PK >@ID
END

PK is primary key of student_details table

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-02-09 : 11:38:15

You may not be able to use newID in the function but you can do this

1) Make a view with


CREATE VIEW my_Rand AS SELECT newID() AS new_Id

2) You can then reference the view from the function and get your newId that way.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-02-09 : 11:55:58

You cannot directly link a stored procedure to a table column, so you will have to generate the passwords with a stored procedure call with each insert.

This thread has several more ideas for generating passwords:
Generate Password Procedure
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78859

CODO ERGO SUM

daipayan
Posting Yak Master

181 Posts

Posted - 2009-02-10 : 09:07:29

Thanks to all the peoples who help me in this query, thankz a lot!

You people just ROCK!
HATS OFF TO ALL OF YOU GUYS!

Daipayan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 09:21:36

you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources