I want to rewrite a stored procedure to user defined function because I
want to insert into a table a subset of the returned columns from stored
procedure.
I want to insert UserName, GroupName and LoginName from sp_helpuser into
a table.
Prease help me how to rewrite this procedure to function.
I test the function like this but is not a correct sintax of function:
create function fn_helpuser (@.name_in_db sysname=null)
returns table
as
begin
return (exec sp_helpuser @.name_in_db)
end
Thanks for help!It's going to be quite a bit more work than that... First check out the
source for it. Put Query Analyzer into Results-in-text mode and do:
use master
GO
EXEC sp_helptext 'sp_helpuser'
GO
--
You'll have to tear that apart and take whatever you need. Very doable, but
not as trivial as just returning it from a function, unfortunately.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mihaly" <Mihaly@.discussions.microsoft.com> wrote in message
news:EC7AA1B5-9CDE-4A71-A3E4-26CF67BEA942@.microsoft.com...
> I want to rewrite a stored procedure to user defined function because I
> want to insert into a table a subset of the returned columns from stored
> procedure.
> I want to insert UserName, GroupName and LoginName from sp_helpuser
into
> a table.
> Prease help me how to rewrite this procedure to function.
> I test the function like this but is not a correct sintax of function:
> create function fn_helpuser (@.name_in_db sysname=null)
> returns table
> as
> begin
> return (exec sp_helpuser @.name_in_db)
> end
> Thanks for help!
>|||Mihaly,
What about creating a table to grab the result of the sp.
Example:
create table #t (
UserName sysname null,
GroupName sysname null,
LoginName sysname null,
DefDBName sysname null,
UserID smallint null,
sid varbinary(85) null
)
insert into #t
execute sp_helpuser
select UserName, GroupName, LoginName from #t
drop table #t
go
AMB
"Mihaly" wrote:
> I want to rewrite a stored procedure to user defined function because I
> want to insert into a table a subset of the returned columns from stored
> procedure.
> I want to insert UserName, GroupName and LoginName from sp_helpuser int
o
> a table.
> Prease help me how to rewrite this procedure to function.
> I test the function like this but is not a correct sintax of function:
> create function fn_helpuser (@.name_in_db sysname=null)
> returns table
> as
> begin
> return (exec sp_helpuser @.name_in_db)
> end
> Thanks for help!
>
Showing posts with label returned. Show all posts
Showing posts with label returned. Show all posts
Wednesday, March 21, 2012
Wednesday, March 7, 2012
Returning Text String.
I am returning a rather large result set using a basic select query.
One of the columns being returned is a char(1) column.
I am wondering if their is a way in sql to return an associated text string
to a char(1) column.
Eg:
If char(1) = 'c' Then text string returned in place place of 'c' should be
contractor.
I am sure i could possibly do this using an if statement on the result
set..of some kind.
Can it be done using any other method?You weren't very specific. Perhaps:
select
case when MyCol = 'c' then 'contractor'
else 'something else'
end
from
MyTable
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"AJ" <AJ@.discussions.microsoft.com> wrote in message
news:B8B7C546-3708-4326-8311-580CF570FF62@.microsoft.com...
I am returning a rather large result set using a basic select query.
One of the columns being returned is a char(1) column.
I am wondering if their is a way in sql to return an associated text string
to a char(1) column.
Eg:
If char(1) = 'c' Then text string returned in place place of 'c' should be
contractor.
I am sure i could possibly do this using an if statement on the result
set..of some kind.
Can it be done using any other method?|||Another way would be to have all of these values in a lookup table and join
to it.
E.g.
Lookup Value, Lookup Name
C, Contractor
Then join to this table on Lookup Value but return Lookup Name in your
select statement
select col1, col2, LookupName
from table
inner join LookupTable
on table.lookupvalue = lookuptable.lookupValue
Hope this helps
Clint Colefax|||Yes you can use a CASE WHEN statement to return the data in the format you
want it. Below is an example of how to do this with your sinaro.
Create TABLE #TEMP
(
EMPTYPE nvarchar (1),
empNAME nvarchar (5)
)
Insert #temp
values ( 'c','Dave')
Insert #temp
values ( 'f','Joe')
Insert #temp
values ( 'p','Rick')
Insert #temp
values ( 'c','Bo')
Select CASE WHEN emptype ='c'
THEN 'Contractor'
WHEN emptype = 'f'
THEN 'FULLTIME'
WHEN emptype = 'p'
THEN 'PARTTIME'
END AS EMPLOYEE_TYPE,
EMPNAME
from #temp
dROP TABLE #TEMP
--
Please refer to books online for more indepth look at how to use CASE
statement.
Hope this helps
JEP
"AJ" wrote:
> I am returning a rather large result set using a basic select query.
> One of the columns being returned is a char(1) column.
> I am wondering if their is a way in sql to return an associated text strin
g
> to a char(1) column.
> Eg:
> If char(1) = 'c' Then text string returned in place place of 'c' should b
e
> contractor.
> I am sure i could possibly do this using an if statement on the result
> set..of some kind.
> Can it be done using any other method?
One of the columns being returned is a char(1) column.
I am wondering if their is a way in sql to return an associated text string
to a char(1) column.
Eg:
If char(1) = 'c' Then text string returned in place place of 'c' should be
contractor.
I am sure i could possibly do this using an if statement on the result
set..of some kind.
Can it be done using any other method?You weren't very specific. Perhaps:
select
case when MyCol = 'c' then 'contractor'
else 'something else'
end
from
MyTable
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"AJ" <AJ@.discussions.microsoft.com> wrote in message
news:B8B7C546-3708-4326-8311-580CF570FF62@.microsoft.com...
I am returning a rather large result set using a basic select query.
One of the columns being returned is a char(1) column.
I am wondering if their is a way in sql to return an associated text string
to a char(1) column.
Eg:
If char(1) = 'c' Then text string returned in place place of 'c' should be
contractor.
I am sure i could possibly do this using an if statement on the result
set..of some kind.
Can it be done using any other method?|||Another way would be to have all of these values in a lookup table and join
to it.
E.g.
Lookup Value, Lookup Name
C, Contractor
Then join to this table on Lookup Value but return Lookup Name in your
select statement
select col1, col2, LookupName
from table
inner join LookupTable
on table.lookupvalue = lookuptable.lookupValue
Hope this helps
Clint Colefax|||Yes you can use a CASE WHEN statement to return the data in the format you
want it. Below is an example of how to do this with your sinaro.
Create TABLE #TEMP
(
EMPTYPE nvarchar (1),
empNAME nvarchar (5)
)
Insert #temp
values ( 'c','Dave')
Insert #temp
values ( 'f','Joe')
Insert #temp
values ( 'p','Rick')
Insert #temp
values ( 'c','Bo')
Select CASE WHEN emptype ='c'
THEN 'Contractor'
WHEN emptype = 'f'
THEN 'FULLTIME'
WHEN emptype = 'p'
THEN 'PARTTIME'
END AS EMPLOYEE_TYPE,
EMPNAME
from #temp
dROP TABLE #TEMP
--
Please refer to books online for more indepth look at how to use CASE
statement.
Hope this helps
JEP
"AJ" wrote:
> I am returning a rather large result set using a basic select query.
> One of the columns being returned is a char(1) column.
> I am wondering if their is a way in sql to return an associated text strin
g
> to a char(1) column.
> Eg:
> If char(1) = 'c' Then text string returned in place place of 'c' should b
e
> contractor.
> I am sure i could possibly do this using an if statement on the result
> set..of some kind.
> Can it be done using any other method?
Saturday, February 25, 2012
Returning Scope_Identity() for GUID
I'm stumped... I'm trying to get the GUID value returned from an insert (I
really need to use a GUID because this is a web app and I want the records t
o
be difficult to find i.e. identity key within range is too easy to browse)
Declare @.Identity nvarchar(30)
Create Table #T (I uniqueidentifier, LName nvarchar(10))
Insert INTO #T (LName) Values('Rosner')
Set @.Identity = Scope_IDENTITY()
SELECT @.IDENTITY
This returns null while
Drop Table #T
Declare @.Identity uniqueidentifier
Create Table #T (I uniqueidentifier, LName nvarchar(10))
Insert INTO #T (LName) Values('Rosner')
Set @.Identity = Scope_IDENTITY()
SELECT @.IDENTITY
returns error - "Operand type clash: numeric is incompativle with
uniqueidentifier.
Suggestions would be greatly appreciated. Thanks in advance.
- Abe1. scope_identity() only returns the last (scoped) identity value - this
value datatype has to be numeric.
2. the first batch returns NULL because there is no identity column in your
table. Thus, the last insert generates a NULL identity (INT) value. The
implicit conversion from INT to nvarchar is allowed. Hence, no error is
raised.
3. the second batch fails because of the implicit conversion. You can't
convert a numeric to uniqueidentifier.
Here is a quick proof of the conversion error:
select convert(uniqueidentifier,1)
-oj
"AbeR" <AbeR@.discussions.microsoft.com> wrote in message
news:5BD89A94-9006-48F3-B73C-426C845E4729@.microsoft.com...
> I'm stumped... I'm trying to get the GUID value returned from an insert (I
> really need to use a GUID because this is a web app and I want the records
> to
> be difficult to find i.e. identity key within range is too easy to browse)
> Declare @.Identity nvarchar(30)
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> This returns null while
> Drop Table #T
> Declare @.Identity uniqueidentifier
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> returns error - "Operand type clash: numeric is incompativle with
> uniqueidentifier.
> Suggestions would be greatly appreciated. Thanks in advance.
> - Abe|||"Returns the last IDENTITY value inserted into an IDENTITY column in the
same scope. A scope is a module -- a stored procedure, trigger, function, or
batch" from BOL .Identity columns need to be of type bigint, int or smallint
and are not compatible with type uniqueidentifier .Therefore,to ask SQL
Server to give you last identity inserted and then assigning that to a
variable previously declared as a uniqueidentifier, equals compatibility
error
To return it do something like:
Declare @.Identity uniqueidentifier
Select @.Identity = newID()
Create Table #T (I uniqueidentifier, LName nvarchar(10))
Insert INTO #T (I,LName) Values(@.identity,'Rosner')
SELECT @.IDENTITY
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"AbeR" <AbeR@.discussions.microsoft.com> wrote in message
news:5BD89A94-9006-48F3-B73C-426C845E4729@.microsoft.com...
> I'm stumped... I'm trying to get the GUID value returned from an insert (I
> really need to use a GUID because this is a web app and I want the records
to
> be difficult to find i.e. identity key within range is too easy to browse)
> Declare @.Identity nvarchar(30)
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> This returns null while
> Drop Table #T
> Declare @.Identity uniqueidentifier
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> returns error - "Operand type clash: numeric is incompativle with
> uniqueidentifier.
> Suggestions would be greatly appreciated. Thanks in advance.
> - Abe|||Hi,
How about capturing the GUID into a local variable and using it for insert
and then as return value as well.
Declare @.PersistGUID uniqueidentifier
Select @.PersistGUID = newid()
Now you could use @.PersistGUID as per your need.
Best Regards
Vadivel
http://vadivel.blogspot.com
"AbeR" wrote:
> I'm stumped... I'm trying to get the GUID value returned from an insert (I
> really need to use a GUID because this is a web app and I want the records
to
> be difficult to find i.e. identity key within range is too easy to browse)
> Declare @.Identity nvarchar(30)
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> This returns null while
> Drop Table #T
> Declare @.Identity uniqueidentifier
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> returns error - "Operand type clash: numeric is incompativle with
> uniqueidentifier.
> Suggestions would be greatly appreciated. Thanks in advance.
> - Abe
really need to use a GUID because this is a web app and I want the records t
o
be difficult to find i.e. identity key within range is too easy to browse)
Declare @.Identity nvarchar(30)
Create Table #T (I uniqueidentifier, LName nvarchar(10))
Insert INTO #T (LName) Values('Rosner')
Set @.Identity = Scope_IDENTITY()
SELECT @.IDENTITY
This returns null while
Drop Table #T
Declare @.Identity uniqueidentifier
Create Table #T (I uniqueidentifier, LName nvarchar(10))
Insert INTO #T (LName) Values('Rosner')
Set @.Identity = Scope_IDENTITY()
SELECT @.IDENTITY
returns error - "Operand type clash: numeric is incompativle with
uniqueidentifier.
Suggestions would be greatly appreciated. Thanks in advance.
- Abe1. scope_identity() only returns the last (scoped) identity value - this
value datatype has to be numeric.
2. the first batch returns NULL because there is no identity column in your
table. Thus, the last insert generates a NULL identity (INT) value. The
implicit conversion from INT to nvarchar is allowed. Hence, no error is
raised.
3. the second batch fails because of the implicit conversion. You can't
convert a numeric to uniqueidentifier.
Here is a quick proof of the conversion error:
select convert(uniqueidentifier,1)
-oj
"AbeR" <AbeR@.discussions.microsoft.com> wrote in message
news:5BD89A94-9006-48F3-B73C-426C845E4729@.microsoft.com...
> I'm stumped... I'm trying to get the GUID value returned from an insert (I
> really need to use a GUID because this is a web app and I want the records
> to
> be difficult to find i.e. identity key within range is too easy to browse)
> Declare @.Identity nvarchar(30)
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> This returns null while
> Drop Table #T
> Declare @.Identity uniqueidentifier
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> returns error - "Operand type clash: numeric is incompativle with
> uniqueidentifier.
> Suggestions would be greatly appreciated. Thanks in advance.
> - Abe|||"Returns the last IDENTITY value inserted into an IDENTITY column in the
same scope. A scope is a module -- a stored procedure, trigger, function, or
batch" from BOL .Identity columns need to be of type bigint, int or smallint
and are not compatible with type uniqueidentifier .Therefore,to ask SQL
Server to give you last identity inserted and then assigning that to a
variable previously declared as a uniqueidentifier, equals compatibility
error
To return it do something like:
Declare @.Identity uniqueidentifier
Select @.Identity = newID()
Create Table #T (I uniqueidentifier, LName nvarchar(10))
Insert INTO #T (I,LName) Values(@.identity,'Rosner')
SELECT @.IDENTITY
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"AbeR" <AbeR@.discussions.microsoft.com> wrote in message
news:5BD89A94-9006-48F3-B73C-426C845E4729@.microsoft.com...
> I'm stumped... I'm trying to get the GUID value returned from an insert (I
> really need to use a GUID because this is a web app and I want the records
to
> be difficult to find i.e. identity key within range is too easy to browse)
> Declare @.Identity nvarchar(30)
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> This returns null while
> Drop Table #T
> Declare @.Identity uniqueidentifier
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> returns error - "Operand type clash: numeric is incompativle with
> uniqueidentifier.
> Suggestions would be greatly appreciated. Thanks in advance.
> - Abe|||Hi,
How about capturing the GUID into a local variable and using it for insert
and then as return value as well.
Declare @.PersistGUID uniqueidentifier
Select @.PersistGUID = newid()
Now you could use @.PersistGUID as per your need.
Best Regards
Vadivel
http://vadivel.blogspot.com
"AbeR" wrote:
> I'm stumped... I'm trying to get the GUID value returned from an insert (I
> really need to use a GUID because this is a web app and I want the records
to
> be difficult to find i.e. identity key within range is too easy to browse)
> Declare @.Identity nvarchar(30)
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> This returns null while
> Drop Table #T
> Declare @.Identity uniqueidentifier
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> returns error - "Operand type clash: numeric is incompativle with
> uniqueidentifier.
> Suggestions would be greatly appreciated. Thanks in advance.
> - Abe
Tuesday, February 21, 2012
Returning First Day of Week
Running SQL 2005 SP2.
I am wondering if there is a simple script or function that, for a given date,
I will get returned the previous Sunday at midnight?
For example, if my given date is today (Wednesday, February 6, 2008), my
return value would be 2/3/2008 00:00:00.000.
Message posted via http://www.droptable.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:7f58a15d04fbc@.uwe...
> Running SQL 2005 SP2.
> I am wondering if there is a simple script or function that, for a given
> date,
> I will get returned the previous Sunday at midnight?
> For example, if my given date is today (Wednesday, February 6, 2008), my
> return value would be 2/3/2008 00:00:00.000.
> --
> Message posted via http://www.droptable.com
>
DECLARE @.dt DATETIME;
SET @.dt = CURRENT_TIMESTAMP;
SELECT DATEADD(DAY,7*FLOOR(DATEDIFF(DAY,'20000102',@.dt)/7.0),'20000102');
David Portas
I am wondering if there is a simple script or function that, for a given date,
I will get returned the previous Sunday at midnight?
For example, if my given date is today (Wednesday, February 6, 2008), my
return value would be 2/3/2008 00:00:00.000.
Message posted via http://www.droptable.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:7f58a15d04fbc@.uwe...
> Running SQL 2005 SP2.
> I am wondering if there is a simple script or function that, for a given
> date,
> I will get returned the previous Sunday at midnight?
> For example, if my given date is today (Wednesday, February 6, 2008), my
> return value would be 2/3/2008 00:00:00.000.
> --
> Message posted via http://www.droptable.com
>
DECLARE @.dt DATETIME;
SET @.dt = CURRENT_TIMESTAMP;
SELECT DATEADD(DAY,7*FLOOR(DATEDIFF(DAY,'20000102',@.dt)/7.0),'20000102');
David Portas
Returning First Day of Week
Running SQL 2005 SP2.
I am wondering if there is a simple script or function that, for a given date,
I will get returned the previous Sunday at midnight?
For example, if my given date is today (Wednesday, February 6, 2008), my
return value would be 2/3/2008 00:00:00.000.
--
Message posted via http://www.sqlmonster.com"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7f58a15d04fbc@.uwe...
> Running SQL 2005 SP2.
> I am wondering if there is a simple script or function that, for a given
> date,
> I will get returned the previous Sunday at midnight?
> For example, if my given date is today (Wednesday, February 6, 2008), my
> return value would be 2/3/2008 00:00:00.000.
> --
> Message posted via http://www.sqlmonster.com
>
DECLARE @.dt DATETIME;
SET @.dt = CURRENT_TIMESTAMP;
SELECT DATEADD(DAY,7*FLOOR(DATEDIFF(DAY,'20000102',@.dt)/7.0),'20000102');
--
David Portas|||When I am working with dates I always stick in an auxillary calendar
table, this is very useful in quickly finding days of weeks etc.
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
I am wondering if there is a simple script or function that, for a given date,
I will get returned the previous Sunday at midnight?
For example, if my given date is today (Wednesday, February 6, 2008), my
return value would be 2/3/2008 00:00:00.000.
--
Message posted via http://www.sqlmonster.com"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7f58a15d04fbc@.uwe...
> Running SQL 2005 SP2.
> I am wondering if there is a simple script or function that, for a given
> date,
> I will get returned the previous Sunday at midnight?
> For example, if my given date is today (Wednesday, February 6, 2008), my
> return value would be 2/3/2008 00:00:00.000.
> --
> Message posted via http://www.sqlmonster.com
>
DECLARE @.dt DATETIME;
SET @.dt = CURRENT_TIMESTAMP;
SELECT DATEADD(DAY,7*FLOOR(DATEDIFF(DAY,'20000102',@.dt)/7.0),'20000102');
--
David Portas|||When I am working with dates I always stick in an auxillary calendar
table, this is very useful in quickly finding days of weeks etc.
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
Subscribe to:
Posts (Atom)