I am dynamically building SQL strings within my code and using EXEC () to excute. My quetions is as follows: Can I assign the ouput of
Exec (@sqlstring) to a variable
i.e
@myvar=exec (@sqlstring)
note: @sqlstring return a single number, count of records.
Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.
So, is this possible using data driven subscriptions? Scenario is:
1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.
Any tips on how to get this working?
Thanks
Mark Smith
If anyone could confirm...
SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.
E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.
First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.
However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.
Question: I thought the filters would remain dynamic and be applied on each sync?
I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!
Question: I wonder why if parent records are supplied, why not child records?
If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...
Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?
I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?
Error:
Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18
Incorrect syntax near the keyword 'external'.
CREATE FUNCTION clrDynamicPivot
(
-- Add the parameters for the function here
@query nvarchar(4000),
@pivotColumn nvarchar(4000),
[code]....
I have a Stored Procedure for processing a Bill of Material.
One column on the Assembly Table is a Function Name that contains some busniess rules.
OK, now I'm doing a Proof of Concept and I'm stumped.
Huuuuh!
I will ultimately have about 100 of these things. My plan was using Dynamic SQL to go execute the function.
Note: The function just returns a bit.
So; here's what I had in mind ...
if isnull(@FnNameYN,'') <> ''
exec spinb_CheckYN @FnNameYN, @InvLineID, @FnBit = @FnBit output
CREATE PROCEDURE dbo.spinb_CheckYN
@FnNameYN varchar(50),
@InvLineID int,
@FnBit bit output
AS
declare @SQL varchar(8000)
set @SQL = '
if dbo.' + @FnNameYN + ' (' + convert(varchar(31),@InvLineID) + ')) = 1
set @FnBit = 1
else
set @FnBit = 0'
exec (@SQL)
GO
Obviously; @FnBit is not defined in @SQL so that execution will not work.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@FnBit'.
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@FnBit'.
So; is there a way to get a value out of a Dynamic SQL piece of code and get that value INTO my OUTPUT variable?
My many thanks to anyone who can solve this riddle for me.
Thank You!
Sigh: For now, it looks like I'll have a huge string of "IF" statements for each business rule function, as follows:
Hopefully a better solution comes to light.
------ Vertical Build1 - Std Vanes -----------
if @FnNameYN = 'fnb_YN_B1_14'
BEGIN
if dbo.fnb_YN_B1_14 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
------ Vertical Build1 - Scissor Vanes -----------
if @FnNameYN = 'fnb_YN_B1_15'
BEGIN
if dbo.fnb_YN_B1_15 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
.
.
.
etc.
I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement.
Using the examples given in Books Online returns compilation errors. See below.
Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement?
James
-- SQL ---------------
EXEC SQL BEGIN DECLARE SECTION;
char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?";
char szLastName[] = "White";
char szFirstName[30];
EXEC SQL END DECLARE SECTION;
EXEC SQL
DECLARE author_cursor CURSOR FOR select_statement;
EXEC SQL
PREPARE select_statement FROM :szCommand;
EXEC SQL OPEN author_cursor USING :szLastName;
EXEC SQL FETCH author_cursor INTO :szFirstName;
--Error--------------------
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near ';'.
Server: Msg 1038, Level 15, State 1, Line 24
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 25
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 27
Line 27: Incorrect syntax near ';'.
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'select_statement'.
Server: Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near 'select_statement'.
Server: Msg 102, Level 15, State 1, Line 35
Incorrect syntax near 'author_cursor'.
Server: Msg 170, Level 15, State 1, Line 36
Line 36: Incorrect syntax near ':'.
I have a requirment which i have partly accomplished , but could not get through completely
i have a file which comes in a standard format ending with date and seq number ,
suppose , the file name is abc_yyyymmdd_01 , for first copy , if it is copied more then once the sequence number changes to 02 and 03 and keep going on .
then i need to transform those in to new file comma delimited destination file with a name abc_yyyymmdd,txt and others counting file counting record abc_count_yyyymmdd.txt. and move it to a designated folder. and the source file is then moved to archived folder
what i have taken apprach is
script task select source file --------------------> data flow task------------------------------------------> script task to destination file
dataflow task -------------------------> does count and copy in delimited format
what is happening here is i can accomlish a regular source file convert it to delimited destination file --------> and move it to destination folder with script task .
but cannot work the dynamic pick of a source file.
please advise with your comments or solution you have
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
I have created a dynamic SQL program that returns a range of columns (1 -12) based on the date range the user may select. Each dynamic column is month based, however, the date range may overlap from one year to another. Thus, the beginning month for one selection may be October 2005, while another may have the beginning month of January 2007.
Basically, the dynamic SQL is a derived Pivot table. The problem that I need to resolve is how do I now use this dynamic result set in a Report. Please keep in mind that the name of the columns change based on the date range select.
I have come to understand that a dynamic anything is a moving target!
Please advise.
I need to pass all values of field of a table into a parameter. My table contains a field called typeID, there are 15 typeID in the table. Currently when a user choses a single type they see a report for that type. I want to give them the ability to run a report on all types. My thinking is that I should just be able to pass all typeID values into a string and pass it to the stored Proc... but being new at sql and this truley being the first thing I have ever done in it... I am missing the mark.
Param is @TypeID
Here is the sql to exec the stored proc:USE [db]
GODECLARE @return_value int
EXEC @return_value = [dbo].[OL]
@TypeID = 2,SELECT 'Return Value' = @return_value
GO
I know this is not the actual SP, I am just trying to get it to work, then I will be able to write the proc. It should be something like @Option = 1 AND 2 or 1 & 2 or something to that nature?
Hi there,I am trying to create a dynamic sql statement as follows:ALTER PROCEDURE [dbo].[GET_FIS_LONGTITLE] -- Add the parameters for the stored procedure here @TABLENAME VARCHAR(25), @COLUMNNAME VARCHAR(25), @COLUMNVALUE VARCHAR(25), @RETURNVALUE VARCHAR(60) OUTASBEGIN DECLARE @SQL AS VARCHAR(4000) SET @SQL = 'SELECT LONGTITLE FROM ' + CAST(@TABLENAME AS VARCHAR(25)) + ' WHERE ' + CAST(@COLUMNNAME AS VARCHAR(25)) + ' = ''' + CAST(@COLUMNVALUE AS VARCHAR(25)) + '''' execute (@SQL) --''' + CAST(@RETURNVALUE AS VARCHAR(60)) + ''' = END here I am trying to get the long title of an item based on the tablename, columnname, the column value. So the select returns the long title from the table as required. But I want to assign that value to the @RETURNVALUE So I tried:SET @SQL = 'SELECT ''' + CAST(@RETURNVALUE AS VARCHAR(60)) + ''' = LONGTITLE FROM ' + CAST(@TABLENAME AS VARCHAR(25)) +
' WHERE ' + CAST(@COLUMNNAME AS VARCHAR(25)) + ' = ''' + CAST(@COLUMNVALUE AS VARCHAR(25)) + ''''
It does not work. I do not know what is missing here.Any help would be greatly appreciated.thanks,Murthy here
Hello All,
I'm trying to build a SQL statement dynamically based on what was selected.
I'm not sure how set strSQL equal to all the items checked in a checklist.
Also, I don't know how to add the item to strSQL once it's determined that it has been checked.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim strSQL As String = 'dynamic SQL statement
Dim li As ListItem
For Each li In cklCheckList.Items
If li.Selected = True Then
'add item to strSQL
End If
Next
End Sub
Thank you!
Tina
Hello Pros
Is it possible to author a stored procedure with dynamic sql inside,
I need the SP for search purposes, take that i have a number of search criterions
represented by a number of text boxes in the front-end web app, so if the users fills up the text box i will include it in the search condition ....
It's so easy to do it in the code-behind file by the means of a dynamic string with the search clauses appended to it, but i wanna do it with SPs
Any help will be highly appreciated,
HI !!
We are creating an Ad Hoc query tool for our Voters Profile system. Voters Profile Table has voting history for person. It has columns like : G04 P04 G03 P03 G02 P02 G01 P01 G00 P00 and History
G = general election P = primary election xx = represent year
G04 etc columns has value = "Y" if person voted in that year else its null
If Person A voted in General Election in year 2004 and Primary Election in 2003 then
History Column = G04P03
Now, we need to create a Ad Hoc Query where people ask for:
"Give me a list of People who votes in All 4 general election"
OR
"Give me a list of people who voted 2 times in Primary Election"
In order to do such query what we should do? Can some one help?
We are currently using SQL Server 7.0. Following are the Dynamic SQL I
try to run
declare
@ls_sql nvarchar(500),
set @ls_sql = 'select count(*) from Member where MemberID'
execute sp_executesql @ls_sql
and it works fine. Now I need the results (Total Counts) into a
varianle. Does any one know the correct syntax? I try following aways,
and it is not work
declare
@ls_sql nvarchar(500),
@ll_count int
set @ls_sql = 'select count(*) from Member where MemberID'
execute sp_executesql @ls_sql, @ll_count
select @ll_count
Thanks
I have a where clause which I dynamically build that looks like this.
SET @orderstatus = ' (t.fieldvalue in ( ''PEND'', ''REDR'') or (t.fieldvalue=''NOTA'' and pr.isactive = 0)
or ((t.fieldvalue=''RXAP'' and m.formularymed = 0))
or (t.fieldvalue=''NOTA'' and pr.deano = @deano)) '
I then do @tsql = 'select * from table where ' + @orderstatus
The problem is I get the literal value @deano not the number that is stored in @deano
Helping a developer with an sp from a .net program which will feed it several parameters (any of which can be null). In other words the where clause is somewhat dynamic. I am posting the select statement (not the parameters) he used. Currently this sp runs very slow. I was considering building some type of dynamic where clause to check for the parameters to create that, but was afraid that in itself might be just as bad. Any better way to handle it?
SELECT *
FROM vGrievances
WHERE
coalesce(GrievanceID,-1) = coalesce( @GID, GrievanceID, -1)
AND coalesce(Member, 'x') = coalesce( @M , Member, 'x')
AND coalesce(Subject,'x') LIKE coalesce( '%' + @S + '%' , Subject,'x')
AND coalesce(GrievDesc,'x') LIKE coalesce('%' + @GD+ '%', GrievDesc,'x')
AND coalesce(OwnerID, -1) = coalesce( @OID, OwnerID, -1)
AND coalesce(CreatorID, -1) = coalesce( @CRID, CreatorID, -1)
AND coalesce(AssignedToID, -1) = coalesce( @ATID, AssignedToID, -1)
AND DueDate >= coalesce(@DDS, DueDate)
AND DueDate <=coalesce(@DDE, DueDate)
AND coalesce(ContractID, -1) = coalesce(@COID, ContractID, -1)
AND coalesce(GrievTypeID, -1) = coalesce(@GTID , GrievTypeID, -1)
AND coalesce(StatusName,'x') = coalesce(@SN, StatusName,'x')
AND CreatedDate >= coalesce(@CDS, CreatedDate)
AND CreatedDate <= coalesce(@CDE, CreatedDate)
AND coalesce(StatusChangedDate,'1/1/1900') >= coalesce(@SCDS , StatusChangedDate,'1/1/1900')
AND coalesce(StatusChangedDate,'1/1/1900') <= coalesce(@SCDE, StatusChangedDate,'1/1/1900')
AND coalesce(Division,'x') = coalesce(@D, Division,'x')
AND coalesce(PriorityTypeID, -1) = coalesce(@PTID, PriorityTypeID, -1)
AND coalesce(OutcomeID, -1) = coalesce(@OcID, OutcomeID, -1)
AND ContractID IN (
(SELECT CONVERT(INT,CONTRACT_ID)
FROM SEC_CONTRACT_SELECT
WHERE SU_USER_ID = @USERID
AND SS_SYS_NAME = 'RESOLVE'))
AND GrievanceID between 1 AND 500
END
GO
Code:
INSERT INTO StagingFinal(col1, col2,
col3,col4, col5, col6, col7, col8)
SELECT stag.id,
REPLACE(stag.col1,stag.col1,'User' + stag.col1)
, us.id , ua.Users
, (CASE WHEN UPPER(Job) = 'Accounting' THEN 0 ELSE 1 END)
, stag.EndDate ,
CONVERT(INT, REPLACE(stag.earnings,'.000',''))
, stag.rep
FROM Staging stag ,
dbo.Users us ,
dbo.UserAccs ua
WHERE stag.cus = ''
AND us.id = ua.id
AND REPLACE(ua.AccountNumber,'-','') = stag.AccountNumber
I have the above code that I want to put into a variable and execute it via EXEC.
I try:
Code:
SET @SQL = ' INSERT INTO StagingFinal(col1, col2,
col3,col4, col5, col6, col7, col8)
SELECT stag.id,
REPLACE(stag.col1,stag.col1,''User'' + stag.col1)
, us.id , ua.Users
, (CASE WHEN UPPER(Job) = ''Accounting'' THEN 0 ELSE 1 END)
, stag.EndDate ,
CONVERT(INT, REPLACE(stag.earnings,''.000'',''''))
, stag.rep
FROM Staging stag ,
dbo.Users us ,
dbo.UserAccs ua
WHERE stag.cus = ''''
AND us.id = ua.id
AND REPLACE(ua.AccountNumber,''-'','''') = stag.AccountNumber '
But no luck... Any help?
Hi:
Can anyone tell me if it is possible to use dynamic sql within a user defined function in MS SQL 2000?
Thanks,
Rui Ferreira
I am trying to create a sql job that has multiple steps, emails out the error on a failure, and will continue to the next step. We can not put Outlook on our servers so we can not use SQL Operators.
Basically I would like every step in the job to jump to a failure step that emails out an error message and returns to the step after the step that failed. I could have a failure step for each actual step, but this job has a number of steps and being the anal programmer I am, I don't want to have multiple steps that do the same thing.
I have figured out how to Dynamically update the current sql Job by using sp_update_jobstep and job tokens [STEPID] and [JOBID]. For example the following code will update the job so it will quit job with failure if the step finishes successfully.
declare @tmpJobID as uniqueidentifier
declare @tmpStepID as integer
set @tmpJobID = (convert(uniqueidentifier, [JOBID]))
set @tmpStepID = [STEPID]
exec sp_update_jobstep @job_id=@tmpJobID, @step_id=@tmpStepID, @on_success_action=2
The problem is that the job is not updated until it is completed. So, the changes will not take affect until the next time it is ran. Is there a way to reload the job into memory, or force the job to use the updates?
I have also tried directly updating the sysjobsteps table but that didn't work either.
Thanks much.
Hi All,
Following is the situation:
DECLARE @iCount INT
DECLARE @strQry AS VARCHAR(4000)
DECLARE @strEmp AS VARCHAR(200)
SET @strEMP = '993,1040,1061'
SET @strQry = 'SELECT @iCount =COUNT(id) FROM tableA WHERE status=91 AND Employee IN ('+ CONVERT(VARCHAR(4000),@strEmp)+')'
EXECUTE (@strQry)
PRINT @iCount
This statement does not execute, I tried to declare @iCount as VARCHAR(10), still it does not work. Actually I wanted to get the count in @iCount and manipulate later.
Can anyone help?
Regards
qa
Hello,
I've tried to convert a static SQL to dynamic SQL but I'm always getting a NULL value
My SP is:
CREATE PROCEDURE usp_dataList
@text nvarchar(100)
AS
DECLARE
@list varchar(8000),
@SQL varchar(600)
SELECT @SQL = 'SELECT '+ @list +'='+COALESCE(@list + ',', '') + 'CAST(data_received.data_data AS varchar(15))
FROM data_received
WHERE data_received.data_data LIKE N'''+Replace(@text,' ',' OR data_received.data_data LIKE N''')+''''
exec(@sql)
SELECT @list AS data_list
GO
What's wrong here?
I've tried to search google and find the problem my self in the past 3 hours but I couldn't find anything wrong (I'm new in SP)
I also tried to change to this:
SELECT @SQL = 'SELECT '+ isnull(@list, '') +'='+COALESCE(@list + ',', '') + 'CAST(data_received.data_data AS varchar(15))
I'm getting this error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
Please help me...
Thanks,
BuildHome
Hi there
I am having problems using some dynamic sql for a crystal report, here is my code.
DECLARE @startdate varchar(20)
DECLARE @enddate varchar(20)
SET @startdate = '11/01/2007'
SET @enddate = '11/30/2007'
SET @SQL = 'SELECT DATEADD(dd,CONVERT(int,CONVERT(int,F4111.ILDGL) % 1000),DATEADD(yyyy,CONVERT(int,F4111.ILDGL / 1000),CONVERT(datetime,''1900-01-01'')) )as ILDGL, F4111.ILTRDJ, F4111.ILKCO, F4111.ILDOC, F4111.ILDCT, F4111.ILDCTO, F4111.ILDOCO, F4111.ILITM, F4111.ILMCU, F4111.ILLITM, F4111.ILLOCN, F4111.ILLOTN, F4111.ILTRQT, F4111.ILTRUM, F4111.ILUNCS,F4102.IBSRP1, F4102.IBSRP2, F4101.IMDSC1 INTO ##F4111t FROM F4111 F4111 INNER JOIN F4102 F4102 ON F4111.ILMCU = F4102.IBMCU INNER JOIN F4101 F4101 ON F4111.ILITM=F4101.IMITM AND F4101.IMITM = F4102.IBITM WHERE DATEADD(dd,CONVERT(int,CONVERT(int,F4111.ILDGL) % 1000),DATEADD(yyyy,CONVERT(int,F4111.ILDGL / 1000),CONVERT(datetime,''1900-01-01'')) )<= ' + @enddate + ' And DATEADD(dd,CONVERT(int,CONVERT(int,F4111.ILDGL) % 1000),DATEADD(yyyy,CONVERT(int,F4111.ILDGL / 1000),CONVERT(datetime,''1900-01-01'')) ) >= ' + @startdate + ''
exec (@SQL)
This does not evaluate the dates properly and when I print the sql string I get the following:
SELECT DATEADD(dd,CONVERT(int,CONVERT(int,F4111.ILDGL) % 1000),DATEADD(yyyy,CONVERT(int,F4111.ILDGL / 1000),CONVERT(datetime,'1900-01-01')) )as ILDGL, F4111.ILTRDJ, F4111.ILKCO, F4111.ILDOC, F4111.ILDCT, F4111.ILDCTO, F4111.ILDOCO, F4111.ILITM, F4111.ILMCU, F4111.ILLITM, F4111.ILLOCN, F4111.ILLOTN, F4111.ILTRQT, F4111.ILTRUM, F4111.ILUNCS,F4102.IBSRP1, F4102.IBSRP2, F4101.IMDSC1 INTO ##F4111t FROM F4111 F4111 INNER JOIN F4102 F4102 ON F4111.ILMCU = F4102.IBMCU INNER JOIN F4101 F4101 ON F4111.ILITM=F4101.IMITM AND F4101.IMITM = F4102.IBITM WHERE DATEADD(dd,CONVERT(int,CONVERT(int,F4111.ILDGL) % 1000),DATEADD(yyyy,CONVERT(int,F4111.ILDGL / 1000),CONVERT(datetime,'1900-01-01')) )<= 11/30/2007 And DATEADD(dd,CONVERT(int,CONVERT(int,F4111.ILDGL) % 1000),DATEADD(yyyy,CONVERT(int,F4111.ILDGL / 1000),CONVERT(datetime,'1900-01-01')) ) >= 11/01/2007
So basically its has not putting ' ' around the date so its causing my code to fail.
There are lots of strange date conversions in this code because I have a Julian date in my source data.
How can I get this to work?
Does anyone know how to execute dynamic SQL in a user defined function? Here's a quick example of what I would like to do. I get an error that only functions and extended stored procedures may be called in a function. Is there any other way to execute dynamic SQL in a UDF?
CREATE function dbo.test(@table char(40), @value char(40))
RETURNS int
AS
BEGIN
DECLARE @return char(3)
DECLARE @sqlstring nvarchar(500)
SET @sqlstring = 'Select count(*) From @table Where id = @value'
Execute sp_executesql @sqlstring
RETURN(@return)
END
I need a procedure accepting series of ID's and retrieve data according to those ID numbers. Like this:
CREATE PROCEDURE tmp
(
@var nvarchar(MAX)
)
AS
select * from Table where ID IN(@var)
I thought of sending ID's like this: '1,2,3'
Of course it doesn't work and gives "can not convert int to nvarchar" error. How can I do this? Dynamic SQL? Or is there a better way?
Anitha writes "I am just a beginner and i need help to solve the below problem in sql
I have 6 records in info table
name id phone
ani 1 999-980-234
ani 1 999-980-145
ani 1 999-980-913
bav 2 888-000-999
bav 2 888-000-991
I want it to be into one record
name id phone1 phone2 phone3
ani 1 999-980-234 999-980-145 999-980-913
bav 2 888-000-999 888-000-991
I want my sql to dynamically create columns and append data
I an do it by assigning the values individually .. which is really tedious .
Appreciate it if you could help me .
Thank you ,
Anitha"
I am passing a FILTER string to a stored procedure and am therefore using dynamic SQL. Is there a better way of handling the following code. I am thinking it would be difficult for someone else to apply changes to the sproc.
@XFilter varchar(1000)AS
Declare @Sql varchar(1000)
Set @Sql = 'SELECT SHPH.*,SHPD.TransCd, SHPD.Vendor, SHPD.Quantity, SHPD.UnitRate,' +
'SHPD.InvoiceNo, SHPD.AmountPaid, SHPD.DatePaid, SHPD.Adjustment, SHPD.USExchRate,' +
'QShipQty = CASE WHEN SHPD.TransCd = ''F'' then Null ELSE SHPD.Quantity END, ' +
'QCost = CASE WHEN SHPD.TransCd = ''F'' then Null ' +
'WHEN SHPH.ProductTaxable = True then ... calc here ... ELSE ... calc ... END, ' +
'PROD.ProductName, PROD.ProductTaxable, PROD.ProductGLAcct, ' +
' (SELECT PSTRate FROM tblCtrl WHERE CtrlID = ''CTRL'') AS PSTRate ' +
'FROM tblShipmentHdr SHPH ' +
'LEFT OUTER JOIN tblProducts PROD ON SHPH.ProductID = PROD.ProductID ' +
'LEFT OUTER JOIN tblShipmentDet SHPD ON SHPH.ShipmentID = SHPD.ShipmentID ' +
@XFilter + ' ORDER BY SHPH.ProductID, SHPH.ReceivedDate'
Exec (@Sql)
any easy tutorial?
thanks
I need a help with the following scenario.
I have two dynamic SQL like this:
DECLARE @sSQL_ID varchar(1000)
SELECT @sSQL_ID= 'SELECT ID FROM Store WHERE StoreLocation='New York'
DECLARE sSQL varchar(1000)
SELECT @sSQL = 'SELECT * FROM City WHERE StoreID = @sSQL_ID
Of course the second statement (the one in bold) is incorrect.
What I need to do is to pass a value returned from first dynamic sql(@sSQL_ID) into second(@sSQL), so it will read something like this SELECT @sSQL = 'SELECT * FROM City WHERE StoreID = 10
Thank you
Hello everyone,
i have created a dynamic sql
create proc pr_dynSql
(
@Param nvarchar(200)
)
as
begin
set @query = 'select * from table1 where '
set @query = @query + @param
exec(@query)
end
this proc works fine. the input passed is the condition
exec pr_dynSql 'firstname like ''sabarish'''
works fine
but when i insert data as
exec pr_dynSql 'firstname like ''neil''obrien'''
i searched for name neil'obrien (note:single quotes over l)
gives error. probably some sort of sql injection how to overcome this .
I have this stored procedure that I am trying to pass a table name as a parameter.
This is my first experiments with dynamic sql and here is my code where I am building the command:
declare @cmd nvarchar(4000)
select @cmd = (N'update ' + @table + N' set totalproductsales = ' + cast(@ppa as nvarchar(50)) +
N', unitssold = ' + cast(@ppaLotionsSold as nvarchar(50)) +
N' where recordid = ' + cast(@recordid as nvarchar(50)))
EXEC sp_executesql @cmd
for some reason @cmd is empty when it hits the sp_executesql statement and I am not sure why. Does anybody have any thoughts?
The Yak Village Idiot
I am a newbie and I was trying to use a local variable to insert
a "where clause" into my T-SQL code, but it won't work. Can anyone give me an idea what I am doing wrong. Here is the code:
DECLARE @WhereClause varchar(500)
SET @WhereClause =
' ([NAME] like ' + CHAR(39) + 'AAA%' + CHAR(39) + ') OR ' + CHAR(13) +
' ([NAME] like ' + CHAR(39) + 'AANEN%'+ CHAR(39) + ') OR ' + CHAR(13) +
' ([NAME] like ' + CHAR(39) + 'ABDEL%' + CHAR(39) + ') OR ' + CHAR(13) +
' ([NAME] like ' + CHAR(39) + 'ABENDR%' + CHAR(39) + ') '
SELECT *
FROM <my table with the field [NAME] in it>
WHERE @WhereClause
The @WhereClause evaluates to:
([NAME] like 'AAA%') OR
([NAME] like 'AANEN%') OR
([NAME] like 'ABDEL%') OR
([NAME] like 'ABENDR%')