Here is the scenario, I have 2 stored procedures, SP1 and SP2
SP1 has the following code:
declare @tmp as varchar(300) set @tmp = 'SELECT * FROM OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'', ''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'
EXEC (@tmp)
SP2 has the following code:
SELECT * FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)
Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used. Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used. Functions - My last resort is to use a table valued function as shown:
FUNCTION MyFunction ( ) RETURNS @retTable ( @Field1 int, @Field2 varchar(50) ) AS BEGIN -- the problem here is that I need to call SP1 and assign it's resulting data into the -- @retTable variable
-- this statement is incorrect, but it's meaning is my goal INSERT @retTableSELECT *FROM SP1
A login named UserLogin1 has db_dataReader and db_dataWriter roles, and three schemas which are db_owner, dbUser1, and dbUser2 in a database named Database1. I wanted to have some tables for read-only, so I right clicked on the table and select properties. In the Permissions tab, I added UserLogin1 and checked Alter, Delete, Insert, Take Ownership, and Update under Deny. It worked for schema dbo only. I did the same steps for the tables that have schema dbUser1 or dbUser2, but UserLogin1 I added didn't stay. Why? How can I make those tables read-only?
How can we use the SQL INSERT statement in the stored procedure to create a table in a user specified filegroup? Table1 will be created by using the following SQL script and will be placed in a default filegroup. The question is can this table been created in a specified filegroup other than default filegroup.
INSERT INTO Table1(col1,col2) SELECT col1,col2 FROM Table2
Example, userA table assign to userA filegroup. userB table assign to userB filegroup. Usually by using CREATE statement you can put the table on a specific filegroup as long as a predefined filegroup has been hard coded in the script. I appreciate any inputs.
I have Logs table and want to assign a serial number to the techs using the following query
Select Date, Case_ID, Site, Dept, Tech, Start_Time, ROW_NUMBER () OVER (PARTITION BY Date, Site, Dept, Tech ORDER BY Start_Time) as Row_Num FROM Logs Where Date = Getdate()
I get the following results.
Date Case ID Site DeptTechStart TimeRow_Num 7/28/14 10023 TartvilleMaintcAmy P.7:301 7/28/14 56789 TartvilleMaintcRem W.8:051 7/28/14 23098 TartvilleMaintcAmy P.8:352 7/28/14 70004 TartvilleMaintcAmy P.9:103 7/28/14 12708 TartvilleMaintcMag O.10:001 7/28/14 10004 TartvilleMaintcAmy P.12:304 7/28/14 40056 TartvilleServiceJoe F.7:301 7/28/14 23458 TartvilleServiceJoe F.7:552 7/28/14 69200 TartvilleServiceRus T. 7:301
Please notice the cases in Maintc department. See how Amy P.'s shift is broken by Rem W. and Mag O. But the Row Number does not recognize this, it still says Amy P's case as 2 and 4 the even though Rem's and Mag's cases were in between.
This is what I really wanted.
Date Case ID Site DeptTechStart TimeRow_Num 7/28/14 10023 TartvilleMaintcAmy P.7:301 7/28/14 56789 TartvilleMaintcRem W.8:051 7/28/14 23098 TartvilleMaintcAmy P.8:351 7/28/14 70004 TartvilleMaintcAmy P.9:102 7/28/14 12708 TartvilleMaintcMag O.10:001 7/28/14 10004 TartvilleMaintcAmy P.12:301 7/28/14 40056 TartvilleServiceJoe F.7:301 7/28/14 23458 TartvilleServiceJoe F.7:552 7/28/14 69200 TartvilleServiceRus T. 7:301
I tried many combination of columns for Partition by () and Order by () and the best I can get is the result at the top. How should I achieve it.
I started with an inline table returning function with a hard coded input table name. This works fine, but my boss wants me to generalize the function, to give it in input table parameter. That's where I'm running into problems.
In one forum, someone suggested that an input parameter for a table is possible in 2012, and the example I saw used "sysname" as the parameter type. It didn't like that. I tried "table" for the parameter type. It didn't like that.
The other suggestion was to use dynamic sql, which I assume means I can no longer use an inline function.
This means switching to the multi-line function, which I will if I have to, but those are more tedious.
Any syntax for using the inline function to accomplish this, or am I stuck with multi-line?
A simple example of what I'm trying to do is below:
Create FUNCTION [CSH388102].[fnTest] ( -- Add the parameters for the function here @Source_Tbl sysname ) RETURNS TABLE AS RETURN ( select @Source_Tbl.yr from @Source_Tbl )
Error I get is:
Msg 1087, Level 16, State 1, Procedure fnTest, Line 12 Must declare the table variable "@Source_Tbl".
If I use "table" as the parameter type, it gives me:
Msg 156, Level 15, State 1, Procedure fnTest, Line 4 Incorrect syntax near the keyword 'table'. Msg 137, Level 15, State 2, Procedure fnTest, Line 12 Must declare the scalar variable "@Source_Tbl".
Does anyone know where to find or how to write a quick user defined fucntionthat will return a table object when passed the string name of the tableobject. The reason why I want dynamicallly set the table name in a storedprocudue WITHOUT using concatination and exec a SQL String.HenceIf @small_int_parameter_previous = 1 then@vchar_tablename = "sales_previous"else@vchar_tablename = "sales"Endselect * from udf_TableLookup(@vchar_tablename )So if I pass 1, that means I want all records from "sales_previous"otherwise give me all records from "sales" (Sales_Previous would last yearssales data for example).udf_TableLookup would I guess lookup in sysobjects for the table name andreturn the table object? I don't know how to do this.I want to do this to avoid having 2 stored procedures..one for current andone for previous year.Please respond to group so others may benfiit from you knowledge.ThanksErik
Ok, 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)
Can someone tell me if it is possible to add an index to a Table variable that is declare as part of a table valued function ? I've tried the following but I can't get it to work.
ALTER FUNCTION dbo.fnSearch_GetJobsByOccurrence ( @param1 int, @param2 int ) RETURNS @Result TABLE (resultcol1 int, resultcol2 int) AS BEGIN
I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.
Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:
I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.
Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:
I have a stored produre. Inside this stored procedure I have table variable with one column. Once the table variable is populated with rows, I would like to pass each value in the table, into a table-valued function. The table-valued function may return any number of rows. I would like all the rows the TVF returns to be returned from the stored procedure as a single result set. I would also like to do this without defining a table variable to hold the results of the table-value function.
Code Snippet
declare @IdTable table ( EmployeeId nvarchar( 16 ) not null ) insert into @IdTable select EmployeeNumber from Employees
/* I need to run this query for every EmployeeId value in @IdTable and return the results from the stored proc as a single result set. */ select * from fn_GetEmployeeById( EmployeeId )
In my stored procedure i have a multi-valued varchar(max) parameter and I wrote a table-valued function that takes the varchar(max) and return a table back to the stored procedure where i inserted into a @table. Just wondering is there a better and faster way of doing this?
ALTER PROCEDURE [dbo].[rpt]
(
@CourtIDs as nvarchar(MAX) -- @CourtIDs = '1231,3432,1234,3421'
) AS
--split CourtIDs into a table DECLARE @tbCourtIDs table(CourtID int NOT NULL PRIMARY KEY) INSERT INTO @tbCourtIDs select * from dbo.Split(@CourtIDs, ',')
This script is for an in-line table function, F_TABLE_NUMBER_RANGE, that generates a number table. The input parameters are the @START_NUMBER and @END_NUMBER. It returns a sorted result set containing all intergers from @START_NUMBER to @END_NUMBER inclusive.
This is an improved version of a script that I posted on a topic a few weeks ago. I modified it to cross join fewer tables based on powers of 16, instead of powers of 2, because I found that this compiled and ran much faster for small result sets (less than 10,000 rows).
This is the link to the other post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=46252&whichpage=5
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[F_TABLE_NUMBER_RANGE]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[F_TABLE_NUMBER_RANGE] GO create function dbo.F_TABLE_NUMBER_RANGE ( @START_NUMBERint, @END_NUMBERint ) /* This function returns an integer table containing all integers in the range of@START_NUMBER through @END_NUMBER, inclusive. The maximum number of rows that this function can return is 16777216. */
returns table as
return ( selecttop 100 percent NUMBER = (a.NUMBER+b.NUMBER)+ -- Add the starting number for the final result set -- The case is needed, because the start and end -- numbers can be passed in any order case when @START_NUMBER <= @END_NUMBER then @START_NUMBER else @END_NUMBER end from ( Selecttop 100 percent NUMBER = convert(int,N01+N02+N03) From -- Cross rows from 3 tables based on powers of 16 -- Maximum number of rows from cross join is 4096, 0 to 4095 ( select N01 = 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 ) n01 cross join ( select N02 = 0 union all select 16 union all select 32 union all select 48 union all select 64 union all select 80 union all select 96 union all select 112 union all select 128 union all select 144 union all select 160 union all select 176 union all select 192 union all select 208 union all select 224 union all select 240 ) n02 cross join ( select N03 = 0 union all select 256 union all select 512 union all select 768 union all select 1024 union all select 1280 union all select 1536 union all select 1792 union all select 2048 union all select 2304 union all select 2560 union all select 2816 union all select 3072 union all select 3328 union all select 3584 union all select 3840 ) n03 where -- Minimize the number of rows crossed by selecting only rows -- with a value less the the square root of rows needed. N01+N02+N03 < -- Square root of total rows rounded up to next whole number convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1))) order by 1 ) a cross join ( Selecttop 100 percent NUMBER = convert(int, (N01+N02+N03) * -- Square root of total rows rounded up to next whole number convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1))) ) From -- Cross rows from 3 tables based on powers of 16 -- Maximum number of rows from cross join is 4096, 0 to 4095 ( select N01 = 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 ) n01 cross join ( select N02 = 0 union all select 16 union all select 32 union all select 48 union all select 64 union all select 80 union all select 96 union all select 112 union all select 128 union all select 144 union all select 160 union all select 176 union all select 192 union all select 208 union all select 224 union all select 240 ) n02 cross join ( select N03 = 0 union all select 256 union all select 512 union all select 768 union all select 1024 union all select 1280 union all select 1536 union all select 1792 union all select 2048 union all select 2304 union all select 2560 union all select 2816 union all select 3072 union all select 3328 union all select 3584 union all select 3840 ) n03 where -- Minimize the number of rows crossed by selecting only rows -- with a value less the the square root of rows needed. N01+N02+N03 < -- Square root of total rows rounded up to next whole number convert(int,ceiling(sqrt(abs(@START_NUMBER-@END_NUMBER)+1))) order by 1 ) b where a.NUMBER+b.NUMBER < -- Total number of rows abs(@START_NUMBER-@END_NUMBER)+1and -- Check that the number of rows to be returned -- is less than or equal to the maximum of 16777216 case when abs(@START_NUMBER-@END_NUMBER)+1 <= 16777216 then 1 else 0 end = 1 order by 1 )
GO GRANT SELECT ON [dbo].[F_TABLE_NUMBER_RANGE] TO [public] GO
-- Demo using the function to ruturn numbers 1 to 2000 select NUMBER from dbo.F_TABLE_NUMBER_RANGE(1,2000)
-- Demo using the function to ruturn numbers -1500 to 2000 select NUMBER from dbo.F_TABLE_NUMBER_RANGE(-1500,2000)
IF @art <>'/' INSERT INTO @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track WHERE artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and artist.artist=@art Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @cd <>'/' insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track where artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cdtitle=@cd Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @tra <> '/' insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track where artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and track.track=@tra Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @gen <>'/' insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track where artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cdtype=@gen Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @cdate<>'01/01/1900' insert into @result SELECT dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track where artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cddate=@cdate Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @label<>'/' insert into @result SELECT dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track where artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and label.label=@label Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf return end --------------------------------------------------------------------- upon running executing this function with valid values i am not getting any results. anything is wrong? thank you,
Dear all, can i write a table return function like this?
create function my_function( returns @MYTABLE table(NAME VARCHAR(20),SES VARCHAR(20),CNT DECIMAL(10),MYDATE DATATIME) insert into @mytable select col1,col2,col3,col4 from tab1 go select col1,col2,col3,col4 from tab2 go select col1,col2,col3,col4 from tab3 go select col1,col2,col3,col4 from tab4 go return end
am i doing correct? what i'm expecting from this function is, i need all the data from select statements should be inserted into one table. please guide me in this regard
Vinod Even you learn 1%, Learn it with 100% confidence.
I'm trying to write a function that I can run with passing the table name as a parameter. I want to return an integer. The tables wll be differnet types (different colums) but they all have a similar field that i want to get a count of. Someting alng the lines of this:
Create FUNCTION [dbo].[fn_GetItemsToPromote] ( @TableName nvarchar(100) ) RETURNS int AS BEGIN Declare @SQL nvarchar(500) return select count(*) from [@TableName]where promote = 1 END
It doesnt like the @TableName. Can anyone show me how to do this correctly?
Table test2 has multiple amounts for each account, I would like to sumthe amounts for the same account and use the result to update thevariable 'tot_amount' in table test1. But SQL does not allow me to usesum function in update. Is there any other way to do this? Thanks.update test1set tot_amount=sum(b.amount)from test1 as ajoin test2 as bon a.acc_no=b.acc_no
Hello, there,I am trying to find out if a table is used by any of the storedprocedures or functions.I can generate all the scripts and look for it. But is there an easyway?THXJohn
I am new to writing table-valued user defined function, so this might be a 'Duh' question. I am trying to write a table-valued UDF that has to return multiple rows. How do I do this?
I have just updated to SQL server Everywhere(Compact) version from SQL Server Mobile. A desktop application was using several features of the Mobile version which no longer seem to be available in the Everywhere version. 'Truncate Table' does not work. This can be worked around with deleting the rows and then changing the Identity seed value. Also 'INSERT INTO Table1 SELECT * FROM Table2 no longer works in SQL Everywhere. Now the number of columns in the two tables must match exactly or a list of columns must be provided with the INSERT and SELECT statements. The biggest probem I have run into so far is that 'SELECT * INTO Table1 FROM Table2' no longer works. How can a new table be created without manually recreating all of the column information?
These functions worked in the SQL Mobile version, why was this functionally removed in the latest Everywhere(Compact) version.
Is there a listing of what is supported in the Everywhere(Compact) version, besides digging through the BOL documentation.
I have to work with some configuration data that is stored in rows as a comma separated values. Something like this:
Key1 A,1,Z,0;B,2,Y,9;C,,8,X;
Key2 Alpha,101;Beta,102;
Each group of data is separated by a semicolon and each value by a comma. The quantity of values may vary from one key to the other. Over this values sometimes I need to run some selects, so I went with the idea to get it as a table using CLR.
There I find the first problem: I didn't find a way to return a "variable" column with a CLR function, I had to create a SP. Ok, now I execute spGetConfigurationAsTable 'Key1' and I can obtain something like this:
A 1 Z 0
B 2 Y 9
C 3 X 8
But I'm faced with a second problem: How can I run a query over this? I didn't find a way to run a query over an output of a SP. And I can't INSERT the result into a temporary table because I cannot CREATE the table static (remember the columns may differ from one configuration to the other).
So, it seemed a pretty simple task and a neat solution, but I'm kinda stuck. Is there a way to run a query over the SP output? Or is there a way to have a variable table output from a CLR UDF?
Here is the code of the CLR SP I use to obtain the data, and also the (non-working) CLR user defined function.
THANKS!
Code Snippet public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void spGetConfigurationAsTable(string Key) { SqlConnection conn = new SqlConnection("Context Connection=true"); string SqlCmd = string.Format("SELECT Value FROM Configuracion WHERE [Key] = '{0}' ", Key); SqlCommand cmd = new SqlCommand(SqlCmd, conn); conn.Open(); string Value = Convert.ToString(cmd.ExecuteScalar()); if (Value.Length > 0) { char SeparatorRow = ';'; char SeparatorColumn = ','; if (Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length > 35) return; StringBuilder SqlCreate = new StringBuilder("DECLARE @Output TABLE ("); for (int i = 0; i < Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length; i++) { SqlCreate.AppendFormat("[{0}] varchar(50),", Convert.ToChar(65 + i)); } SqlCreate.Remove(SqlCreate.Length - 1, 1); SqlCreate.AppendLine(");"); StringBuilder SqlInsert = new StringBuilder(); foreach (string row in Value.Split(SeparatorRow)) { if (row.Length > 0) { SqlInsert.Append("INSERT INTO @Output VALUES ("); // busca las diferentes "columns" ~ Charly foreach (string column in row.Split(SeparatorColumn)) { SqlInsert.AppendFormat("'{0}',", column); } SqlInsert.Remove(SqlInsert.Length - 1, 1); SqlInsert.AppendLine(");"); } } string SqlSelect = "SELECT * FROM @Output;"; cmd.CommandText = SqlCreate.ToString() + SqlInsert.ToString() + SqlSelect; SqlDataReader reader = cmd.ExecuteReader(); SqlContext.Pipe.Send(reader); reader.Close(); reader.Dispose(); } conn.Close(); conn.Dispose(); cmd.Dispose(); } };
Code Snippet public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static IEnumerable fGetConfigurationAsTable(string Key) { SqlConnection conn = new SqlConnection("Context Connection=true"); string SqlCmd = string.Format("SELECT Value FROM Configuracion WHERE [Key] = '{0}' ", Key); SqlCommand cmd = new SqlCommand(SqlCmd, conn); conn.Open(); string Value = Convert.ToString(cmd.ExecuteScalar()); conn.Close(); conn.Dispose(); cmd.Dispose(); DataTable dt = new DataTable(); if (Value.Length > 0) { char SeparatorRow = ';'; char SeparatorColumn = ','; if (Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length > 35) { // throw exception } string ColumnName; for (int i = 0; i < Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length; i++) { ColumnName = string.Format("[{0}] varchar(50),", Convert.ToChar(65 + i)); dt.Columns.Add(ColumnName, Type.GetType("System.String")); } foreach (string row in Value.Split(SeparatorRow)) { if (row.Length > 0) { dt.Rows.Add(row.Split(SeparatorColumn)); } } } return dt.Rows; } };
I want to add a record into a table, using a function. This function needs to give back the number of records of a given table.
Function ImportResults
@NumberOfRecords int @TableName varchar(60) @ImportDate datetime @Result bit
@Result = 1 @ImportDate =getdate()
--Open Table with tablenames
select TableName from Tbl_ImportTables For each record in Tbl_ImportTables
@TableName = Tbl_ImportTables.TableName @NumberOfRecords = select count(*) from @TableName -- add to Import Results Insert into Tbl_Import_Results
(ImportDate ,TableName ,NumberOfRecords ) VALUES ( @ImportDate , @TableName , @NumberOfRecords ) -- Check 0 records (I only want to know if there is a table with 0 records) IF @NumberOfRecords=0 then @Result=0 Next record
RETURN @Result
End function
Can somebody help me with the right syntax (I am new to functions and sp's)? Is this a scalar valued function?
I create a function to call stored procedure, and query a view, but I am having troubles with quering table-value function. I looked for an example on the net on how to do it, but I couldn't find any (I find only .net examples :-) )
Can anyone direct me to an example, or write a small sample?
Helo all, i dont know if that is the right way, but i was wanted to make kind of sql profiler on a webpage :) and cool features would be to usa ajax to fire it async in some time secvence.. ok, ok, here goes my code and expla... i have sql2005 (express edition,, cose i think its so cool and for a lot of projects can be used for), attached startup procedure, whit> sp_procoption 'kreni_instanco_moja', startup, true, and kreni_insta... is like: ----------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[kreni_instanco_moja]ASBEGINexec kp.dbo.start_traceEND---------------- ok and my start_trace is: ----------------ALTER PROCEDURE [dbo].[start_trace]ASBEGINDECLARE @traceidnum INT,@on BIT,@file_path NVARCHAR(50),@maxsize bigint;SET @on = 1;SET @maxsize =5;SET @file_path = 'O:BU_DATABASE' + convert(varchar,getdate(), 102);EXEC sp_trace_create @traceid = @traceidnum OUTPUT, @options = 2, @tracefile = @file_path, @maxfilesize = @maxsize, @stoptime=null, @filecount =0;EXEC sp_trace_setevent 2, 12, 1, @on -- ili 12 ili 13EXEC sp_trace_setstatus 2, 1 -- 1 = START, 0 = STOPEND---------------- actual code that brings table and shows sql executed is: ----------------use kpdrop table traceselect textdata into trace fromfn_trace_gettable(N'O:BU_DATABASE' + convert(varchar,getdate(), 102) + '.trc',1)select textdata from tracego---------------- and it works fine,, but when its fired from my local admin connection, and try that whit IUSR*???? not going... ok, i think that IUSR have to have privilage to open that O:BU_DATABASE ... file, and to have priv to drop tabel and to fire that function..fn_trace_get ... ?? so, that is where i got stuck up... any help would be veryyyy apreciated... hey, we can put some kind of project if anybody is interested! :) to have admin page and to monitor sql code that is executed,,, and remotely if i was not clear in some explaining, you can ask me and ill try better thenx, kpendic
hi all i am using VS 2005 with SQL Server 2005 and i faced a problem that need to be solved urgently... i want to make a function that take a table as input parameter which is the output of a stored procedure (Record set)... first i found that to make w table be as input parameter you must create type of that table first but i found that sql server 2005 doesn't have the 'table' as a type... please any help will be appreciated thanks in advance
I have two tables. One has Projects with the total amt of hours worked on the project itself. The other is an Employee_Projects table with individual rows of hrs per employee worked on the above referenced projects.
I need to SUM all the hrs from the Employee_Projects table and GROUP BY project number, then UPDATE the Projects table with the sum of hours where the Project Number from table A matches the Project Number from table B.
Of course, you cant use an aggregate function in an UPDATE clause, so what would be the easiest way to do this??
I'm writing reports in Rep. Services that reads data from Dynamics NAV (Navision). In NAV data are stored by company and this is implemented by using the company name as prefix to the table name. This means that in a NAV database with three companies (lets call these companies A, B and C) we will have three tables with customers. The table names will be A$Customer, B$Customer and C$Customer.
Now to my problem:
I wan't to write one report where I can choose company. I do not want to use a stored procedure. I want to use a function so I can use the function in select statements and join several functions to build up a report that needs data from several tables.
Is there some way to pass the table name or a part of the table name to a function that returns the content of the actual table? I know I can pass parameters that I can use in the where clause, but is it possible to do it with the table name. Or is there any other way to solve this.
