| How to get result sets when using dynamic query Posted: 05 Apr 2013 01:15 AM PDT This procedure returns ID and Question ID . I previously was sending data at application level by appending them in varchar variable but in dynamic query m unable to do it. I tried to get the resultset at application level by ISingleResult as i have connected application through Linq to SQL but it is also not working please help . CREATE PROCEDURE [dbo].[Get_Ques_id_for_can](@candidate_id varchar(max),@Exam_id varchar(max),@sec_id bigint,@Q_id varchar(max) OUTPUT)ASBEGINdeclare @table_query varchar(max)declare @table varchar(max)set @table=@candidate_ID+@Exam_iddeclare @id varchar(50)declare @ques_id varchar(50)set @table_query='SELECT [ID], [Question_ID] FROM ['+@table+'] WHERE [Section_ID]='+CONVERT(varchar(50),@sec_id)EXEC(@table_query)END |
| Need urgent help Posted: 04 Apr 2013 11:26 PM PDT Hi All,I have a table like this. but its have 5-6k rows.1 AppName2 AppVersion3 Product4 Date15 Date26 Date37 AppName8 AppVersion9 Product10 Date111 Date212 Date313 AppName14 AppVersion15 Product16 Date117 Date218 Date3.....and I want result like this. the logic is that after each 6 records we have a complete row. you can also say that column name are associated with records.AppName AppVersion Product Date1 Date2 Date31 2 3 4 5 67 8 9 10 11 1213 14 15 16 17 18Thanks for your help.Sneh |
| How to get all 7 days Name in One table. Posted: 04 Apr 2013 02:46 AM PDT I am working on one report which showing all days name as column.but Now I am getting some bugs.If for particular day data is not available on table then that days name are missing on report and which is not good.If table doesn't have that day data even we want to show that day column as null.can any one help me out with this??? |
| Passing Multiple Values As Parameters to get Multiple Columns with Comma Seperation(MSSQL) Posted: 04 Apr 2013 10:06 PM PDT Create procedure temp(@MID smallint)asBeginselect TranID,[MonthValue]=(CASE WHEN @MID=1 THEN Jan WHEN @MID=2 THEN Feb WHEN @MID=3 THEN Mar WHEN @MID=4 THEN Apr WHEN @MID=5 THEN May WHEN @MID=6 THEN Jun WHEN @MID=7 THEN Jul END) FROM TblTran as M where TranID=1 and M.Month = @MIDendThis is a stored procedure with a parameter @MID that i'm using to generate a report using SSRS.If a single value is passed to the parameter it works fine.For example-Transaction Table[b][u]TranID | Apr | May | Jun | Jul [/u][/b] 1 | 50 | 30 | 11 | 30 2 | 51 | 39 | 100 | 30if i execute with[b]Exec 4[/b]the result is what i expect[b][u]TranID | MonthValue [/u] [/b] 1 | 50 **-- ie Aprils value**But I need to pass multiple values to the parameterlike[b]exec 4,5,6[/b]and desired result should be[u][b]TranID | MonthValue [/b] [/u] 1 | 50,30,11 ***-->Comma Separated values of columns how can i acheive result like this?? |
| Concatenate fields by specifying the start position of the next filed Posted: 04 Apr 2013 08:27 PM PDT Hi,how do I Concatenate fields by specifying the start position of the next filed.e.g. I have table_x with three columnsNameLastNameEmpnoI would like to concatenate the three so that I only get one line. but I would also like to specify the length of the column and where the next column would start. |
| query help Posted: 04 Apr 2013 07:37 AM PDT Hi Please help for below requirement.I need to combine two columns of data and insert in new columnex: if have col1 has 1, 2, 3 and col2 has 1, 2, 3 and i need new column as col3 by combining col1 and col2 like 11, 22, 33I have two columns liek belwocol1 col21 2 1 56 12 7I need table like belowCol1 col2 col31 2 121 5 156 1 622 7 27ThanksAswin |
| TSQL to get users and permissions Posted: 04 Apr 2013 03:26 AM PDT I would like to use the below code in a cursor and loop through each and every database in an instance. Now I want to select the name of the database along the with the details the script selects. Is this possible?[code="sql"]select [Login Type]=case sp.typewhen 'u' then 'WIN'when 's' then 'SQL'when 'g' then 'GRP'end,convert(char(45),sp.name) as srvLogin, convert(char(45),sp2.name) as srvRole,convert(char(25),dbp.name) as dbUser,convert(char(25),dbp2.name) as dbRolefrom sys.server_principals as sp joinsys.database_principals as dbp on sp.sid=dbp.sid joinsys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id joinsys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left joinsys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id[/code] |
| Get the one set of values from the same table Posted: 04 Apr 2013 03:20 AM PDT Hi,I have a table which has following columnsShipmentID, ProductID, ProductType,QtyNow I want to select only those records which has only productTypeID = 2 for exampleShipmentID ProductID ProductType Qty1 1 2 101 2 2 51 3 3 12 1 2 102 2 2 5The query should return only ShipmentID = 2, because that set only has producttypid = 2. I want to avoid those rows if it has at least one producttypeid = 3.I tried different queries but it's not giving me the perfect result.Can anybody help me out please?Thanks.Thanks.Gunjan. |
