Am abit new to SQL Server Programming and I have an issue which I need Assistance.
I have a table with the following Colunms
Inv_Number
ItemCode1
ItemCode2
ItemCode3
ItemCode4
ItemCode5
this table as data which i want to put to another table with the following Columns
Invoice_Number
ItemCode
Amount
What i mean here is that Invoice Items were static and were about five and the new programs am designing will have more item codes.
Hi,
I am trying to create a inline function which is listed below.
USE [Northwind]
SET ANSI_NULLS ON
GO
CREATE FUNCTION newIdentity()
RETURNS TABLE
AS
RETURN
(SELECT ident_current('orders'))
GO
while executing this function in sql server 2005 my get this error
CREATE FUNCTION failed because a column name is not specified for column 1.
Pleae help me to fix this error
thanks
Purnima
Create a Function call InitCap
accepts 1 parameter of type varchar
returns a varchar
It should only capitalize the first letter of the string passed to it.
Items to watch out for.
a-z not starting the string
The 1st letter is already capitalized
This problems deals with ASCII characters, just wanted to get some insight here.
Hi im trying to create a function called SQLExecute that takes an SQL query, executes it and returns the resultant dataset in dsetResponse and if an error in strError, however i am unsure if whether im on the right track or not, where would i put the sql query and what else needs to be done, my code is as follows;public static DataSet SQLExecute(string strSQL, string strError)
{
DataSet dsetResponse = new DataSet();
try
{using (SqlConnection conn = new SqlConnection(DHOC.clsDHOC.GetConnString()))
{SqlCommand cmd = new SqlCommand();cmd.CommandType = CommandType.Text;
}
}catch (ThreadAbortException thEx)
{throw;
}catch (Exception ex)
{
string strError
}return dsetResponse;
}
Hi,
When I try to create a CLR function in SQL 2005 (June CTP) I get the following error:
Msg 6505, Level 16, State 1, Procedure Extenso, Line 1Could not find Type 'Extenso' in assembly 'ExtensoNET'.
The assembly registers successfully, with no errors.
I use the following command to create the function:CREATE FUNCTION Extenso (@Valor float)RETURNS VARCHAR(255)AS EXTERNAL NAME ExtensoNET.Extenso.EscreveExtensoGO
The function's code is the following:
using System;using System.Collections.Generic;using System.Text;using Microsoft.SqlServer.Server;using System.Data.SqlClient;
namespace ExtensoNET{ public class Extenso { [SqlFunction(DataAccess = DataAccessKind.Read)] public static string EscreveExtenso(double? nValor) {
//Valida Argumento if (nValor==null || nValor <= 0 || nValor > 999999999.99) return "";
//Variáveis int nTamanho; string cValor, cParte, cFinal; string[] aGrupo = { "", "", "", "", "" }; string[] aTexto = { "", "", "", "", "" }; . . . }
return cFinal;
} }}
Thanks in advance,
Anderson
Alright, I'll write this again. The first try didn't go so well. I am learning SQL Server from an online tutorial. Maybe there are a lot who do that, maybe not, but it's a good tutorial. There are projects to work on at the end of each section, and they involve more than just regurgitating everything I just read. They even make me figure out stuff that wasn't covered in the section (which is probably a mistake, but it makes the learning more interesting and I like it). The problem is that I've hit one of those places and I can't figure it out. The last project for this section is to write a function that calculates and displays the diameter of the base, the circumference of the base, the base area, the side area, the total area, and the volume, given the radius and the height of a cylinder. Now I had to do the same thing as a query for the previous section, and hunting down all the formulae involved was more time-consuming than writing the thing, but I'm stumped here. Every example and work-along project was, for lack of a better description, "one function, one result". So how do I get a function to return multiple results? If I've posted this in the wrong place, someone please let me know where I might find the information I need. For those of you who may see this and be stunned that I haven't figured out the obvious, remember you were new once too. Thanks in advance.
Now that I've seen some of the other posts, maybe I should clarify. Here's what I had from the last project.
declare @Radius float(10),
@BaseDiameter float(10),
@BaseCircumference float(10),
@BaseArea float(10),
@Height float(10),
@SurfaceArea float(10),
@TotalArea float(10),
@Volume float(10)
set @Radius = 19.1
set @BaseDiameter = @Radius * 2
set @BaseCircumference = @BaseDiameter * PI()
set @BaseArea = power(@Radius,2) * PI()
set @Height = 16.27
set @SurfaceArea = 2 * (pi() * @Radius * @Height)
set @TotalArea = 2 * (pi() * @Radius * @Height) + 2 * (power(@Radius,2) * PI())
set @Volume = (power(@Radius,2) * pi()) * @Height
select @Radius as [Base Radius],
@BaseDiameter as [Base Diameter],
@BaseArea as [Base Area],
@Height as Height,
@SurfaceArea as [Side Area],
@TotalArea as [Total Area],
@Volume as Volume
go
So how do I make a function that does the same thing?
Schiz
When I declare a cursor,I use a variable to replace the sql statement:DECLARE rs CURSOR LOCAL FAST_FORWARD FOR@sqlPlanBut it is not true.Who can correct for me.Another question is :How to execute a sql statement state by a variable "@sqlPlan" andinsert the result to a table "@FeatRequestStatus"?I am a new hand of sql programming.Thank you very much for your help
View 2 Replies View RelatedI had succesfully created a CLR function which was fully operational on the development server. Today the powers that be decided to do a full restore from the Production server because the wanted the most recent data on the server. Since our code has not been deployed to production I went through the exercise of redeploying everything to the server and all goes well until I try to create the function:
Code Snippet
CREATE FUNCTION f_GetGroupMembership(@providerKey nvarchar(200), @connection nvarchar(1000))
RETURNS TABLE (groupID uniqueidentifier, groupName nvarchar(100), groupType nvarchar(25))
AS
EXTERNAL NAME NavigatorSecurity.groups.GetGroupsForUser
This returns an error:
Msg 10305, Level 16, State 1, Procedure f_GetGroupMembership, Line 1
The Init method for a CLR table-valued function must be annotated with SqlFunctionAttribute.
Now remember this CLR was working correctly previous to the restore from backup. But just in case I got a case of dumb*** I checked my clr code anyhow here it is:
Code Snippet
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.DirectoryServices;
using System.Collections;
public class GroupMembership
{
[SqlFunctionAttribute(FillRowMethodName = "FillGroupRow")]
public static IEnumerable GetGroupsForUser(String ProviderKey, String Connection)
{
return groups.GetGroupsForUser(ProviderKey, Connection);
}
public static void FillGroupRow(Object obj, out SqlGuid guid, out SqlString name, out SqlString type)
{
//cast the enumerator passed in
group grp = (group)obj;
//create output parameters
guid = new SqlGuid(grp.guid);
name = new SqlString(grp.Name);
type = new SqlString(grp.applicationData);
}
}
Can anyone tell me why this is happening? How can I overcome this problem?
Help will be highly appreciated.
I have compiled a dll with the following code:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Public Class SICTrans
Public Shared Function TransSIC(ByVal inpSIC As String) As String
'Dim conn As SqlConnection = New SqlConnection("context connection=true")
Dim NewSIC, TempSIC, tempFSIC As String
If Len(NZ(inpSIC)) > 0 Then
TempSIC = NZ(inpSIC)
If Len(TempSIC) < 5 Then TempSIC = Left("00000", 5 - Len(TempSIC))
tempFSIC = Left(TempSIC, 2) + "." + Mid(TempSIC, 3, 2)
If Val(Right(TempSIC, 1)) > 0 Then
tempFSIC = tempFSIC + "/" + Right(TempSIC, 1)
End If
End If
NewSIC = tempFSIC
TransSIC = NewSIC
End Function
Public Shared Function NZ(ByVal input As String) As String
If Not (input Is Nothing) Then
Return input
Exit Function
End If
Return String.Empty
End Function
End Class
Which compiles fine...
i then use the following code to create the assembly in SQL which is fine:
USE NARD
GO
CREATE ASSEMBLY SICCodeTrans
FROM 'c:SICCodeTrans.dll'
WITH PERMISSION_SET = SAFE
GO
but when i goto create the function with the following code it wont have it!
CREATE FUNCTION TransSICCode(@inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.SICTrans.TransSIC
GO
It gives me the following error message
Msg 6505, Level 16, State 1, Procedure TransSICCode, Line 1
Could not find Type 'SICTrans' in assembly 'SICCodeTrans'.
Any ideas??????????
Thanks
Marek Kluczynski
SQL Server 2000 Standard SP4 Build 2187.
When we are creating the function below the compilation process append one row at the end of the function text:
Code Snippet
ALTER FUNCTION [dbo].[FX_LOJA_REPOSICAO] (
@sParametros varchar(8000),
@sFiltroFiliais varchar(8000),
@sFiltroProdutos varchar(8000)
)
RETURNS @RESULT TABLE (
FILIAL VARCHAR(25) COLLATE DATABASE_DEFAULT NOT NULL,
PRODUTO CHAR(12) COLLATE DATABASE_DEFAULT NOT NULL,
COR_PRODUTO CHAR(10) COLLATE DATABASE_DEFAULT NOT NULL,
COD_TIPO SMALLINT NOT NULL,
DESC_TIPO VARCHAR(40) COLLATE DATABASE_DEFAULT,
QTDE AS (T1+T2+T3+T4+T5+T6+T7+T8+T9+T10+T11+T12+T13+T14+T15+T16+T17+T18+T19+T20+T21+T22+T23+T24+
T25+T26+T27+T28+T29+T30+T31+T32+T33+T34+T35+T36+T37+T38+T39+T40+T41+T42+T43+T44+T45+T46+T47+T48),
T1 SMALLINT, T2 SMALLINT, T3 SMALLINT, T4 SMALLINT, T5 SMALLINT, T6 SMALLINT,
T7 SMALLINT, T8 SMALLINT, T9 SMALLINT, T10 SMALLINT,T11 SMALLINT,T12 SMALLINT,
T13 SMALLINT,T14 SMALLINT,T15 SMALLINT,T16 SMALLINT,T17 SMALLINT,T18 SMALLINT,
T19 SMALLINT,T20 SMALLINT,T21 SMALLINT,T22 SMALLINT,T23 SMALLINT,T24 SMALLINT,
T25 SMALLINT,T26 SMALLINT,T27 SMALLINT,T28 SMALLINT,T29 SMALLINT,T30 SMALLINT,
T31 SMALLINT,T32 SMALLINT,T33 SMALLINT,T34 SMALLINT,T35 SMALLINT,T36 SMALLINT,
T37 SMALLINT,T38 SMALLINT,T39 SMALLINT,T40 SMALLINT,T41 SMALLINT,T42 SMALLINT,
T43 SMALLINT,T44 SMALLINT,T45 SMALLINT,T46 SMALLINT,T47 SMALLINT,T48 SMALLINT,
FILIAL_ORIGEM VARCHAR(25) COLLATE DATABASE_DEFAULT,
FILIAL_DESTINO VARCHAR(25) COLLATE DATABASE_DEFAULT,
ORIGEM_DESTINO VARCHAR(25) COLLATE DATABASE_DEFAULT NOT NULL,
PACK VARCHAR(20) COLLATE DATABASE_DEFAULT,
PRIMARY KEY NONCLUSTERED (FILIAL, PRODUTO, COR_PRODUTO, COD_TIPO, ORIGEM_DESTINO) )
AS
...FUNCTION BODY...
RETURN
END
SP_HELPTEXT returns the function text with the row bellow appended:
Code Snippet
...FUNCTION BODY...
RETURN
END
(T1+T2+T3+T4+T5+T6+T7+T8+T9+T10+T11+T12+T13+T14+T15+T16+T17+T18+T19+T20+T21+T22+T23+T24+
T25+T26+T27+T28+T29+T30+T31+T32+T33+T34+T35+T36+T37+T38+T39+T40+T41+T42+T43+T44+T45+T46+T47+T48)
Why this happening....
Any suggestions?
I am writing a SQLServer script that I want to be schema name independent. I mean I know that all users of the script will have the same tables, but not necessarily the same schema name.
When I hard code the script to use the name of my schema, wcadmin, it works OK.
CREATE FUNCTION wcadmin.dectohex(@a numeric)
RETURNS varchar(8)
BEGIN
DECLARE @x varchar(8)
DECLARE @y varchar(1)
DECLARE @z numeric
DECLARE @w numeric
SET @w=@a
SET @x=''
WHILE @w > 0
BEGIN
SET @z = @w % 16;
SET @y= CASE @z
WHEN 10 THEN 'A'
WHEN 11 THEN 'B'
WHEN 12 THEN 'C'
WHEN 13 THEN 'D'
WHEN 14 THEN 'E'
WHEN 15 THEN 'F'
ELSE CAST(@z AS varchar)
END
SET @w = ROUND(@w/16,0,1)
SET @x = @y + @x
END
-- Pads the number with 0s on the left
SET @x = RIGHT(REPLICATE('0',8) + @x ,8)
RETURN @x
END;
GO
select 'WTDOCUMENT' HOLDER,
dm.WTDocumentNumber ITEMNUMBER,
dm.name ITEMNAME,
ad.fileName ContentFilename,
fh.hostName VaultHost,
wcadmin.dectohex(fi.uniqueSequenceNumber) VaultFile,
fm.path VaultPath
from
WTDocument di,
WTDocumentMaster dm,
HolderToContent hc,
ApplicationData ad,
FvItem fi,
FvFolder ff,
FvMount fm,
FvHost fh
where di.idA3masterReference = dm.idA2A2
and fm.idA3A5 = ff.idA2A2
and fm.idA3B5 = fh.idA2A2
and fi.idA3A4 = ff.idA2A2
and ad.idA3A5 = fi.idA2A2
and hc.idA3B5 = ad.idA2A2
and hc.idA3A5 = di.idA2A2
DROP FUNCTION wcadmin.dectohex;
GO
But when I remove my schema name I get the error
'dectohex' is not a recognized built-in function name.
In this case I'm just using :-
CREATE FUNCTION dectohex(@a numeric)
.
.
.
.
select 'WTDOCUMENT' HOLDER,
dm.WTDocumentNumber ITEMNUMBER,
dm.name ITEMNAME,
ad.fileName ContentFilename,
fh.hostName VaultHost,
dectohex(fi.uniqueSequenceNumber) VaultFile,
.
.
.
.
DROP FUNCTION dectohex;
Creating and dropping the function seems to work OK when I drop the schema name, I just can't call it.
I've been trying various permutations of dbo and [dbo] prefixes unsuccessfully.
Any suggestions?
Thanks
David
i have a function
Create Function ReturnAmountB(@CMID int) Returns Decimal
as
BEGIN
declare @Return decimal
select @Return =sum(PD_PaymentAmount) from Payment_Details where
PD_IsRefund=1 and PD_PaymentType=0 and PD_CMID=@CMID
return @Return
END
when i run this on server(remote) this error shows...
Incorrect syntex near 'Function'
Must declare the variable '@CMID'
A RETURN statement with a return value cannot be used in this context
i am not able to understand why this error shows...
Any solution!!!
spandey
Hello,I am learning SQL Server 2005. I have (correctly) written in .NETassembly DemoSQLServer with aggregate function AvgNoMinMax in classDemo and I have added assembly to database DemoSQLServer. Now I needto create aggregate in SQL Server. I tried this way:CREATE AGGREGATE AvgNoMinMax(@v float) RETURNS float EXTERNAL NAME[DemoSQLServer].[DemoSQLServer.Demo].[AvgNoMinMax]Unfortunately I have error:Incorrect syntax near '.'.I don't know what's wrong. Please help.Thank you very much!/RAM/
View 1 Replies View Related
hi to all,
i have written SQL-CLR function using C# which will perform some manupulation and will not not return any value(return type is void)
i am creating sql function using SQL script
CREATE FUNCTION dbo.Amex ()
RETURNS NULL
AS CALLER
WITH EXECUTE
AS
EXTERNAL
NAME [AMEX].[UserDefinedFunctions].[Function1];
GO
but i am getting error.
there is something wrong in my sql syntax..can anybody help me?
when creating funtion having signature
public static void Function1()
i am getting error.......................
Error 1 The method "Function1" in class "UserDefinedFunctions" marked as a user defined function must return a scalar value or ISqlReader. SqlServerProject1
can anybody help me?
thanks in advance
Chetan S. Raut
I am need to create comma separated list in sql and I am using below query.
declare @ConcatenatedString NVARCHAR(MAX)
select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR)
from
(
select 1 Number
union
select 2 Number
union
select 3 Number
)rslt
select @ConcatenatedString
When I use the above code inside a function, i am not getting desired output.
create function GetConcatenatedValue
AS
(
declare @ConcatenatedString NVARCHAR(MAX)
select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR)
from
(
select 1 Number
union
select 2 Number
union
select 3 Number
)rslt
return @ConcatenatedString
)
Hi all,
I've created a number of tables, views, sproc, and functions whose names begin with "sys_", but when I tried to create a multi-statement table-valued function with this type of name, I got:
Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 9
System table 'sys_test' was not created, because ad hoc updates to system catalogs are not enabled.
I had a quick look in this forum for 1706 (and on Google) but couldn't find anything. Does anyone know for certain if this is a bug in SQL2K?
Thanks, Jos
Here's a test script:
/*
----------------------------------------------------------------------------------------------------
T-SQL code to test creation of three types of function where the function name begins with "sys_".
Jos Potts, 02-Nov-2006
----------------------------------------------------------------------------------------------------
*/
PRINT @@VERSION
go
PRINT 'Scalar function with name "sys_" creates ok...'
go
CREATE FUNCTION sys_test
()
RETURNS INT
AS
BEGIN
RETURN 1
END
go
DROP FUNCTION sys_test
go
PRINT ''
go
PRINT 'In-line table-valued function with name "sys_" creates ok...'
go
CREATE FUNCTION sys_test
()
RETURNS TABLE
AS
RETURN SELECT 1 c
go
DROP FUNCTION sys_test
go
PRINT ''
go
PRINT 'Multi-statement table-valued function with name "sys_" generates error 1706...'
go
CREATE FUNCTION sys_tmp
()
RETURNS @t TABLE
(c INT)
AS
BEGIN
INSERT INTO @t VALUES (1)
RETURN
END
go
DROP FUNCTION sys_test
go
PRINT ''
go
/*
----------------------------------------------------------------------------------------------------
*/
And here€™s the output from running the test script in Query Analyser on our server:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Scalar function with name "sys_" creates ok...
In-line table-valued function with name "sys_" creates ok...
Multi-statement table-valued function with name "sys_" generates error 1706...
Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 11
System table 'sys_tmp' was not created, because ad hoc updates to system catalogs are not enabled.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the function 'sys_test', because it does not exist in the system catalog.
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
View 3 Replies View RelatedHi all,
I executed the following sql script successfuuly:
shcInLineTableFN.sql:
USE pubs
GO
CREATE FUNCTION dbo.AuthorsForState(@cState char(2))
RETURNS TABLE
AS
RETURN (SELECT * FROM Authors WHERE state = @cState)
GO
And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.
I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:
shcInlineTableFNresult.sql:
USE pubs
GO
SELECT * FROM shcInLineTableFN
GO
I got the following error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'shcInLineTableFN'.
Please help and advise me how to fix the syntax
"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.
Thanks in advance,
Scott Chang
Hi,
If I want to automatically insert a record which has default value in a table,
how can I create the trigger?
I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...
ali
Got some errors on this one...
Is Rand function cannot be used in the User Defined function?
Thanks.
I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, so here is a link to the relevant code: [URL] ...
I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working.
Hi,
I wonder if there a function that i can use in the expression builder that return a value (e.g o) if the input value is null ( Like ifnull(colum1,0) )
i hope to have the answer because i need it so much.
Maylo
Can anybody know ,how can we add builtin functions(ROW_NUMBER()) of Sql Server 2005 into database library.
I get this error when i used into storeprocedure :
ROW_NUMBER() function is not recognized in store procedure.
i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library.
I need to add that function into MS SQL SERVER 2005 database library.
Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz
View 3 Replies View RelatedI am in need of creating a new user for stored procedures execution say sa (to whom i am in need of creating under my user defined db)
View 1 Replies View RelatedOk, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table. I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table. Great. I pass in the table name, and sure enough, it comes back with a record set, 1 row per column. That's exactly what I need.
Umm... This is the part where I'm at a loss. The stored procedure outputs the resultset as a resultset (Not as an output param). Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work. But... How do I use the resultset from the stored procedure? You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output. Ugh. Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys. Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key. Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error. Ugh. Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
SELECT *
FROM Split(@tmp,DEFAULT)
Hi All
Yesterday Peso was gracious enough to help me with creating function/views/sp's
I took those examples and extended what had from excel into function in SQL
however I see myself repeating certain parts of the query and i'm wondering if there is a way to call a function (in part or in whole) from another function?
Here are excerpts two functions I have:
We'll call this function UserUsage()
------------------------------------
RETURN(
SELECT ut.LastName, ut.FirstName,
CEILING(Sum(hu.session_time)/ 60000) AS [Time Spent(MIN)],
Max(hu.time_stamp) AS [Last Log Date],
pct.Title, cat.topic_name
FROM ZSRIVENDEL.dbo.UserTable ut,
ZSRIVENDEL.dbo.history_usage hu,
ZSRIVENDEL.dbo.pc_CourseTitles pct,
ZSRIVENDEL.dbo.cam_topics cat
WHERE ut.student_id = hu.student_id
AND hu.course_id = pct.CourseID
AND hu.topic_id = cat.topic_id
AND ((ut.ClientID=@ClientID)
AND (pct.ClientID=@ClientID)
AND (ut.GroupID=3400)
AND (hu.time_stamp>= @StartDate
And hu.time_stamp< @EndDate)
AND (hu.session_time<21600000))
GROUP BY ut.LastName, ut.FirstName, pct.Title, cat.topic_name
)
and will call this function UserSummary():
-----------------------------------------
RETURN (
SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]
FROM ZSRIVENDEL.dbo.UserTable AS ut
INNER JOIN ZSRIVENDEL.dbo.History_Usage AS hu
ON hu.Student_ID = ut.Student_ID
WHERE ut.ClientID = @ClientID
AND ut.GroupID = 3400
AND hu.Time_Stamp >= @StartDate
AND hu.Time_Stamp < @EndDate
AND hu.Session_Time < 21600000
GROUP BY ut.LastName, ut.FirstName
)
As you can see the first part of the both query are simlar. In particular the:
SELECTut.LastName, ut.FirstName,
CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]
and also the variables @StartDate and @EndDate.
In C# it would create a method and just call that method as well as the variables. However i'm not sure how to do that with sql functions. Could someone shed some light please?
Thank you!
Hi,I have written a stored proc with some temporary tables and also useda getdate() in my stored proc. When i try to call the sproc the erroris that we can only use extended sprocs or function inside a sproc.Now if try to write the stored proc directly inside a fuction ie copypaste after changing my temp tables to tables the problem is , i get aerror invalid use of getdate in sproc.What do i do to get somethingfor my results inside a table.Thanks in advance.RVG
View 5 Replies View RelatedHi,I have a User Defined Datatype, which I want to use to define anotherdata-type. I tried the obvious:EXEC sp_addtype@typename = UDT_OBJECT_ID,@phystype = 'NUMERIC (5)',@nulltype = 'NULL'GOEXEC sp_addtype@typename = UDT_TRACKING_NUM,@phystype = UDT_OBJECT_ID,@nulltype = 'NOT NULL'GObut that didn't work :Msg 15036, Level 16, State 1, Server SKINNER, Procedure sp_addtype, Line 186The data type UDT_OBJECT_ID does not exist.Has anyone done this before,Thanks,Rowland.
View 2 Replies View RelatedI am new to SQL Server and have a few questions:
1) We have given our clients the option to create a scheduled job for a future date. When this occurs, the jobID, the new action for the job, and the future date is inserted into a table aptly called 'Scheduling'. What we are hoping SQL Server can do is the following:
Query the 'Scheduling' table based on the current date to see if there are any jobs that need their actions updated.
If ( 1 ) returns a recordset, update the job's action in the 'Jobs' table with the new action
Delete the rows that were queried and updated from the 'Scheduling' table
I am assuming I can do this using the schedule Jobs (excuse the irony) in SQL Server Management. Is this true?
2) I having been playing with TSQL to do the previous mentioned. My other question is how can I query the database using the current date? For example, the date in the database is entered as "mm/dd/yyyy" and I can query it using the following: SELECT * FROM Scheduling WHERE Date='6/30/2006', which will return the recordsets that I desire. If I can schedule SQL Server to do this, then how will I query it based on today's date? SELECT * FROM Scheduling WHERE Date="today's date". I tried the function GETDATE(), but that didn't seem to work. Any ideas?
Thanks
Hello-
I am using VB .NET 2003 with SQL Server Express 2005. I am writing a program that creates a new database each time it is ran (through "Test_My_Connection" Sub below). On my development PC I am able to create a database on the fly using the code below without any issue:
Once I deploy to any computer I get the following error on the Create_DB_File below:
"An attempt to attach an auto-named database for file c:Program FilesCommon FilesIdeasmyTestDB.mdf failed. As database with that name exists, or specified file cannot be opened, or it is located on UNC share."
I can create any database file I want with this code on my design PC, as long as it is in the "c:program filescommon filesideas" directory, if I move the directory I get the error on my design PC also. Do I need to register the directory I am using as a data directory to allow my PC or deployment computers to create databases? I don't know how I got it to work in the "common files" directory for my development PC but it does. Any thoughts? Code is below, with connection string...
Thanks,
Brian
'This will be run each time a new project is started
Private Sub Test_My_Connection
Dim myProjName as String ="myTestDB"
myOutDB_cls = New OutputDBCls(retMsg)
If retMsg <> "SUCCESS" then
'Deal with it
End If
'Database files to be created by "Create_DB_File" Sub
myOutDB_cls.mdf_file = "c:program filescommon filesideas" & myProjName & ".mdf"
myOutDB_cls.ldf_file = "c:program filescommon filesideas" & myProjName & ".ldf"
'Go and create the output database with tables
myOutDB_cls.Create_DB_File(retMsg) 'create the project database
End Sub
'This is myOutDB_cls definition
Public Sub New(ByRef retMsg As String)
ConnectionString = "Data Source=.SQLExpress"
conn = New SqlConnection(ConnectionString)
Open_Connection(retMsg) 'Used as a test
End Sub
'Opens connection
Private Sub Open_Connection(ByRef retMsg As String)
Try
conn.Open()
Catch ex As Exception
retMsg = ex.Message & "----" & "Open_Connection"
Exit Sub
End Try
retMsg = "SUCCESS"
End Sub
Public Sub Create_DB_File(ByRef retMsg As String)
Dim sql as string = vbnullstring
Try
If conn.State <> ConnectionState.Open Then ' Open the connection if not open
conn.Open()
End If
sql = "CREATE DATABASE " & fileName & " ON PRIMARY" + "(Name=test_data, filename = '" & mdf_file & "', size=3," + "maxsize=5, filegrowth=10%)log on" + "(name=" & fileName & "_log, filename='" & ldf_file & "',size=3," + "maxsize=20,filegrowth=1)"
Execute_SQL_Stmt(sql, retMsg)
retMsg = "SUCCESS"
Catch ex As Exception
retMsg = ex.Message & "----" & "Create_DB_File"
Finally
conn.Close()
End Try
End Sub
Hi,
I need to convert the following .NET code to a SQL Server 2005 user defined function. Let me know where to start
Here is the code in ASP.NET using VB
Shared Function GetDigest(ByVal tamperProofParams As String) As String
Dim Digest As String = String.Empty
Dim input As String = String.Concat(SecretSalt, tamperProofParams, SecretSalt)
'The array of bytes that will contain the encrypted value of input
Dim hashedDataBytes As Byte()
'The encoder class used to convert strPlainText to an array of bytes
Dim encoder As New System.Text.UTF8Encoding
'Create an instance of the MD5CryptoServiceProvider class
Dim md5Hasher As New System.Security.Cryptography.MD5CryptoServiceProvider
'Call ComputeHash, passing in the plain-text string as an array of bytes
'The return value is the encrypted value, as an array of bytes
hashedDataBytes = md5Hasher.ComputeHash(encoder.GetBytes(input))
'Base-64 Encode the results and strip off ending '==', if it exists
Digest = Convert.ToBase64String(hashedDataBytes).TrimEnd("=".ToCharArray())
Return Digest
End Function
The above code is taken from this URL
http://aspnet.4guysfromrolla.com/articles/083105-1.aspx
Thanks