Dynamic Derivation Of Heading - Is It Possible
Nov 10, 2005
Hi
I have a query which produces effectively a pivottable. Is there any way I can dynamically assign the column headings ie the code on each line after AS rather than hard coded as I have currently
Extract of Current SP
CREATE PROC dbo.FairValeSummaryPivot
@BatchRunID INT
AS
SET NOCOUNT ON
SELECT
MIN(CASE WHEN Tn = '1' THEN PVBalance ELSE 0 END) AS 'Tn1 - Tn0' ,
MIN(CASE WHEN Tn = '0' THEN PVBalance END) AS 'Tn0 - Tn-1' ,
MIN(CASE WHEN Tn = '-1' THEN PVBalance END) AS 'Tn-1 - Tn-2',
MIN(CASE WHEN Tn = '-2' THEN PVBalance END) AS 'Tn-2 - Tn-3',
MIN(CASE WHEN Tn = '-3' THEN PVBalance END) AS 'Tn-3 - Tn-4',
MIN(CASE WHEN Tn = '-4' THEN PVBalance END) AS 'Tn-4 - Tn-5',
-- and so on
FROM FVSummary
WHERE BatchRunID = @BatchRunID
GO
what I would like would be along the lines of
MIN(CASE WHEN Tn = '1' THEN PVBalance ELSE 0 END) AS 'Tn' + Tn + ' - Tn' + Tn-1, ,
Hope this is clear
Cheers
View 3 Replies
ADVERTISEMENT
Dec 23, 2004
we have a table called evaluation_questions, the table has following fields
queId,Question, level, parentId
the queId is primary key(auto number),where as the field "question" will have question, heading or the subheading,
the level describes the hierarchy of the field "Question", 0 means its a heading, 1 means a subheading and 2 means a question.
where as the parentId means describes the immediate parentId, like if it is 1, then the parent will be English heading....
queIdQuestionlevelparentId
1English00
2Reading11
3Writing11
4recognizing words12
5Fluency22
so English is a heading as level is 0 and has no parent as parentId is also 0.
Reading is a subheading as level is 1 and has a prent English as its parentId=1 which is the queId oof English
same is the case with writing
where as recognizing words and fluency both are questions as the level is 2 and their parentId is 2 which means they come under reading.
Output:
Now What i want is to retrieve the all the questions and headings under a specified heading.like if i pass parentID as a parameter to stored
procedure i should get all the headings and questions under a specified parentID.i need to fill the dataset with it.
View 3 Replies
View Related
Feb 27, 2008
Hi there,
I am a new member of this site and I am not very much aware of T-sql's working.
My question is what if I need to get one column's data to be the heading of another column.
To be very exact I have a school's database. The table I am talking about is of the results of students. The table contains Student ID, Subject ID, Total marks of the subject, Marks obtained in the subject. Now I want to print a report by generating data from this table. Right now the data is something like this
StuID - - - SubID - - - -Tot - - -Obt
1 - - - - - - -1 - - - - - - -50 - - - 38
1 - - - - - - -2 - - - - - - -50 - - - 41
1 - - - - - - -3 - - - - - - -50 - - - 42
1 - - - - - - -4 - - - - - - -50 - - - 40
2 - - - - - - -1 - - - - - - -50 - - - 35
2 - - - - - - -2 - - - - - - -50 - - - 40
2 - - - - - - -3 - - - - - - -50 - - - 42
2 - - - - - - -4 - - - - - - -50 - - - 41
StudentID and SubjectID fields are related to other tables so I can get the names from there but when I need the report I need the data in the form of
StuID - Sub 1 - - - Sub 2 - - - Sub 3 - - - -Sub4
1 - - - - 38 - - - - - - 41 - - - - - - 42 - - - - - - 40
2 - - - - 35 - - - - - - 40 - - - - - - 42 - - - - - - 41
The Subjects can be different for different students so the query should be dynamic instead of hard coding the names of the subjects. I hope I am clear with my question. The subjectIDs or their names will become the headings and they will contain the obtained marks for that subjects in their columns just for the reports. I have also checked the PIVOT function but was not able to do what I wanted.
Thanks.
View 9 Replies
View Related
Dec 2, 2005
OOPS Sudden inspiration - got it now
Guys
Spent all morning on this - hope someone can help
I've got some select code :
declare @BusinessDate DATETIME
set @BusinessDate = '28 Feb 2005'
select
--?? as period
tdbe.eventid , tdbe.tradeid ,tdbe.cashflowstartdate
from HDD_t_HA HA
join hdd_t_Hedge_Instrument HI on HA.HAId = HI.HAId
join hdd_t_TDB_Event TDBE on HI.EventId = TDBE.EventId
join hdd_t_TDB TDB on TDBE.TradeId = TDB.TradeId
where ha.haid = 29 and
ha.validfrom <= getdate() and ha.validto > getdate()
and tdbe.effectivefrom <= @BusinessDate and tdbe.effectiveto > @BusinessDate
and tdb.effectivefrom <= @BusinessDate and tdb.effectiveto > @BusinessDate
--and tdbe.tradeid = 117
order by tdbe.tradeid , cashflowstartdate
Which produces :
eventid tradeid cashflowstartdate
121102004-04-19 00:00:00.000
122102004-07-19 00:00:00.000
123102004-10-19 00:00:00.000
124102005-01-19 00:00:00.000
125102005-04-19 00:00:00.000
126102005-07-19 00:00:00.000
127102005-10-19 00:00:00.000
128102006-01-19 00:00:00.000
68101172004-11-16 00:00:00.000
68111172005-05-16 00:00:00.000
68121172005-11-16 00:00:00.000
68131172006-05-16 00:00:00.000
68141172006-11-16 00:00:00.000
68151172007-05-16 00:00:00.000
68161172007-11-16 00:00:00.000
68171172008-05-16 00:00:00.000
68181172008-11-16 00:00:00.000
68191172009-05-16 00:00:00.000
68201172009-11-16 00:00:00.000
68211172010-05-16 00:00:00.000
68221172010-11-16 00:00:00.000
68231172011-05-16 00:00:00.000
I'm trying to derive a 'period' value which is an integer based on the ordering of cashflowstartdatefor each tradeid group. The desired effect is as below
Period eventid tradeid cashflowstartdate
1 121102004-04-19 00:00:00.000
2 122102004-07-19 00:00:00.000
3 123102004-10-19 00:00:00.000
4 124102005-01-19 00:00:00.000
5 125102005-04-19 00:00:00.000
6 126102005-07-19 00:00:00.000
7 127102005-10-19 00:00:00.000
8 128102006-01-19 00:00:00.000
1 68101172004-11-16 00:00:00.000
2 68111172005-05-16 00:00:00.000
3 68121172005-11-16 00:00:00.000
4 68131172006-05-16 00:00:00.000
5 68141172006-11-16 00:00:00.000
6 68151172007-05-16 00:00:00.000
7 68161172007-11-16 00:00:00.000
8 68171172008-05-16 00:00:00.000
9 68181172008-11-16 00:00:00.000
10 68191172009-05-16 00:00:00.000
11 68201172009-11-16 00:00:00.000
12 68211172010-05-16 00:00:00.000
13 68221172010-11-16 00:00:00.000
14 68231172011-05-16 00:00:00.000
Hope this makes and thanks in advance
View 1 Replies
View Related
Mar 29, 2007
Hello:
I have a situation where our users will key into a parameter field the name for the heading. I callled the parameter heading VarHdg1. How do I go about doing this? I tried printing @VarHdg1 but nothing came out. Please advise.
Thanks
View 1 Replies
View Related
Aug 13, 2004
I have a table like this
EMPnoSERISSTATUS jOB POINTS
1AAPOC12
1BAQCS5
2CPREP4
2AASOS1
5AATDS4
1APVSS320
and i need a query or Stored prodedure to get data in the below format
EMPNOABC
133250
2104
5400
Note: A, B,C ... are field name in table (SERIS) and i would like that data as the column heading.
I dont want to use below query , as we dont know the value is dyanamic
SELECT empno,
SUM(CASE seris WHEN <B>'A'</B> THEN POINTS END ) AS <B> 'A' </B>
SUM(CASE seris WHEN <B> 'B' </B> THEN POINTS END ) AS <B>'B'</B>
FROM TABLE1
GROUP BY EMPNO
View 2 Replies
View Related
Sep 20, 2005
I am trying to change the row and column heading to make it more meaningful in MDX.
For example instead of showing the_actual_cost, i want to show cost. This is done easily in SQL like
SELECT the_actual_cost AS cost......
Any help will be greatly appreciated.
Thanks
Mahesh
View 1 Replies
View Related
May 4, 2007
I'm trying to create a heading for my report.
I want
sales figures between 09/11/2006 to 09/04/2007.
The date comes from parameters
What I have done is created 4 text boxes
textbox 1 = sales figures between
textbox 2 = =datepart("D", Parameters!param_datef.Value) & "/" & datepart("M", Parameters!param_datef.Value) & "/" & datepart("YYYY", Parameters!param_datef.Value)
textbox 3 = To
textbox 4 = =datepart("D", Parameters!param_datet.Value) & "/" & datepart("M", Parameters!param_datet.Value) & "/" & datepart("YYYY", Parameters!param_datet.Value)
Ok the problem is the text boxes when previewed are all over the place.
In crystal reports you can add a formula into a text box after text
Is this sort of thing possible in Reporting services
Thanks for all your help
View 3 Replies
View Related
May 14, 2008
Hello,
I have a simple question, can someone provide syntax to wrap a column heading?
Example: Select sum(netprice) as 'Current YTD Invoice Amount'
I would like to see the output's column heading as:
Current YTD
Invoice Amount
----------------------
####.##
Thank you in advance,
Maria
View 1 Replies
View Related
Jan 31, 2007
Hello :
In the heading of the report, I want to put one text area, or i put a datum of the base.
He says to me :
Fields cannot be used in headings o feet of page.
Have you ideas??
Thank you very much
View 1 Replies
View Related
Jan 7, 2015
I would like to make the column heading to be the current year for the Sales I'm adding below.
SELECT dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no,dbo.arslmfil_SQL.slspsn_name,
SUM(CASE WHEN year(getdate()) = qivsalesmtdytdcustsalesperson.year THEN Sales END) AS convert(varchar(4),year(getdate()))
FROM dbo.QIVSalesMTDYTDCustSalesPerson INNER JOIN
dbo.arslmfil_SQL ON dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no = dbo.arslmfil_SQL.humres_id
GROUP BY dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no, dbo.arslmfil_SQL.slspsn_name
What I have now gives me incorrect syntax near keyword convert.
View 2 Replies
View Related
Sep 20, 2007
Hi All,
I have never used PIVOT before but looks exactly what I want for this scenario:
I have rows of dates associated with ID of Hotels and Room avalability for each Hotel/Date..... I want to show the sum of the rooms per date as columns I am using something like this:SELECT dbHotelID ,[09/20/2007]as [Today],[09/21/2007]as [Today+1],[09/22/2007]as [Today+2] FROM vwRoomAvailable PIVOT (SUM(dbRoomNumber) FOR AvailableDate IN ([09/20/2007], [09/21/2007], [09/22/2007])) AS pAs you can see I know how may days I want in advance so know how many columsn so its not dynamic.. I just dont know what the dates are:I would like to do something like:
DECLARE @todayDate varchar(255),
DECLARE @todayPlusOne varchar(255),
DECLARE @todayPlusTwo varchar(255) SET @todayDate = CONVERT(CHAR, GETDATE(),101)SET @todayPlusOne = CONVERT(CHAR, DATEADD(d, 1, GETDATE(),101)SET @todayPlusTwo = CONVERT(CHAR, DATEADD(d, 2, GETDATE(),101) SELECT dbHotelID,@todayDate as Today,@todayPlusOne as [Today+1],@todayPlusTwo as [Today+2] FROM vwRoomAvailable PIVOT (SUM(dbHotelRoomAvailabilityNumber) FOR AvailableDate IN ([@todayDate], [@todayPlusOne], [@todayPlusTwo])) AS pBut I can’t seem to put the variable in the PIVOT value list or GETDATE() Anyone got any ideas or do I just try and do this another way and forgot PIVOT. I am using sql server 2005 express.
Thanks in advance.
Lee
View 5 Replies
View Related
Mar 12, 2008
In SSRS/RDL, How do you format a column heading to use carriage returns?
I have a couple of instance where I have a column heading that I want spread over 3 lines. For example, the column heading "= Actual Amount" I would want centered and displayed on 3 lines, as follows:
=
Actual
Amount
View 4 Replies
View Related
Nov 30, 2015
We run 2014 enterprise. I tried this with both table and matrix controls to no avail.
In the table scenario, I drag the table control over, instruct ssrs that a group name will go into column 1 and a sales figure in column2. Then I highlight the sales figure cell, add a column group on month number and generate my report off some june and july data.
ssrs understands that the months now expand horizontally but the rows alternate one with june filled in (blanks in july) and the next with july filled in for the same group name. I believe I got all my sort by conditions set but am not sure.
I tried all sorts of combos in the tablix and group properties before giving up.
View 3 Replies
View Related
Apr 14, 2015
I need to print in RED if there's atleast 1 or more consistency error and need a heading color of the table (Caption) how to do it.
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>DB Corruption Report:</H1>' +
N'<table border="1">' +
N'<tr><th>DBName</th><th>MessageText</th>' +
[code]....
View 1 Replies
View Related
Jun 3, 2015
I am using Column Groups:
And I am getting:
Note how Today is being repeated three times. What I want is Today appearing once and centered (merged cell). Is this possible?
View 9 Replies
View Related
Dec 14, 2012
i just clicked on Advanced mode in Column Group, and then in Row Group Side i set Fixed Data=true for first top static. I'm using local report not server report and i'm displaying that local report in Reportviewer. Now also its not working....
View 6 Replies
View Related
Aug 25, 2007
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
View 12 Replies
View Related
Nov 23, 2007
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
View 3 Replies
View Related
May 2, 2007
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?
View 4 Replies
View Related
Mar 9, 2015
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]....
View 1 Replies
View Related
Mar 24, 2007
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.
View 10 Replies
View Related
Oct 24, 2004
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 ':'.
View 2 Replies
View Related
Apr 15, 2008
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
View 14 Replies
View Related
Mar 2, 2014
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.
View 3 Replies
View Related
Mar 9, 2007
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.
View 3 Replies
View Related
Sep 21, 2007
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?
View 6 Replies
View Related
Oct 3, 2007
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
View 2 Replies
View Related
Apr 25, 2004
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
View 17 Replies
View Related
Nov 21, 2004
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,
View 2 Replies
View Related
Apr 25, 2005
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?
View 7 Replies
View Related
Dec 20, 1999
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
View 1 Replies
View Related
May 9, 2005
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
View 2 Replies
View Related
